If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with design
I have a database which I have constructed which has many tables. It needs
redesigning as I don’t think it’s an effective database design, as when trying to achieve tasks its always giving me trouble. The db was originally designed with jus 9 tables. I now have to make amendments to accommodate expansion. What I am trying to get the system to do. The db holds a list of customers. The customers can enter the system via an agent, or via the client making an appointment with a client. Whether the customer enters the system via an agent or via the client, I need to record all the meters details, and renewal dates. I then need to keep a status of the new clients which are coming into the system. When a new client has been successful, I will update the system to allow the full meter details to be entered into the system. The flow of the system is as follows. Customer into system (agent = non appointment, client = appointment) Record all preliminary meter details Keep a status of all the clients. If successful with client record all meter details. Also allow to record special meter details. An employee can enter any customer into system and also make appointments, for customers. They can also enter agents and agent personnel into the system. I was wandering if someone could help point out weakness in design if I emailed it, or point me to a somewhere with good design solutions. Any help on a more efficient design would be appreciated. I do know I have to change the customer table, to accommodate many address. Thanks Ronnie |
#2
|
|||
|
|||
Ronnie
Not really enough information to go on... What if I said "I have a vehicle with 3 features but it needs more, what should I do?"? I don't understand the relationships among customers, clients and agents. I don't understand how meters and meter details are involved. I don't understand renewal dates -- what's being renewed, for whom? I don't understand agent personnel. ?!Status?! ?!Many addresses?! I don't understand! -- More info, please ... Jeff Boyce Access MVP "Ronnie" wrote in message ... I have a database which I have constructed which has many tables. It needs redesigning as I don’t think it’s an effective database design, as when trying to achieve tasks its always giving me trouble. The db was originally designed with jus 9 tables. I now have to make amendments to accommodate expansion. What I am trying to get the system to do. The db holds a list of customers. The customers can enter the system via an agent, or via the client making an appointment with a client. Whether the customer enters the system via an agent or via the client, I need to record all the meters details, and renewal dates. I then need to keep a status of the new clients which are coming into the system. When a new client has been successful, I will update the system to allow the full meter details to be entered into the system. The flow of the system is as follows. Customer into system (agent = non appointment, client = appointment) Record all preliminary meter details Keep a status of all the clients. If successful with client record all meter details. Also allow to record special meter details. An employee can enter any customer into system and also make appointments, for customers. They can also enter agents and agent personnel into the system. I was wandering if someone could help point out weakness in design if I emailed it, or point me to a somewhere with good design solutions. Any help on a more efficient design would be appreciated. I do know I have to change the customer table, to accommodate many address. Thanks Ronnie |
#3
|
|||
|
|||
Sorry Jeff
As there are many tables I could not fit all the info in. To clarify though I have the following tables as they stand, where the 1 donates the 1 side and the M, the many side. Tables Relationships Suppliers 1 Services M Services 1 Sales Details M Sales 1 Sales details M Agent 1 Reps M Rep 1 RepCustomers M Customers 1 RepCustomers M Customers 1 Appointments M Appointments 1 Status M Employee 1 Appointments M Customers 1 Meters M Customers 1 STD M As I said I know I have to split my customer table to Customer has Many Address’s, I would also like to relate some customer to others, E.G Head Company, etc. The db is based around customers who can have many meters. A customer can be entered into system via an agent who supplies customer leads, or simply by our own leads via an appointment. All meter details must be recorded. All customer meters details entered into the system must be processed. Some are processed immediately whereas others will depend on the customer giving you a response. If a customer accepts then this is processed as a sale. The sale will be a service from a supplier. The customer can also have some special kind of meters called STD. These are only recorded when this is agreed with the customer as a sale. An employee can make many appointments as the amount of appointments determines the employee commission, etc. An agent can have many reps, and when a rep gets customer leads, then the customer can belong to any agent/rep. I hope this makes more sense. Thanks Ronnie "Jeff Boyce" wrote: Ronnie Not really enough information to go on... What if I said "I have a vehicle with 3 features but it needs more, what should I do?"? I don't understand the relationships among customers, clients and agents. I don't understand how meters and meter details are involved. I don't understand renewal dates -- what's being renewed, for whom? I don't understand agent personnel. ?!Status?! ?!Many addresses?! I don't understand! -- More info, please ... Jeff Boyce Access MVP "Ronnie" wrote in message ... I have a database which I have constructed which has many tables. It needs redesigning as I don’t think it’s an effective database design, as when trying to achieve tasks its always giving me trouble. The db was originally designed with jus 9 tables. I now have to make amendments to accommodate expansion. What I am trying to get the system to do. The db holds a list of customers. The customers can enter the system via an agent, or via the client making an appointment with a client. Whether the customer enters the system via an agent or via the client, I need to record all the meters details, and renewal dates. I then need to keep a status of the new clients which are coming into the system. When a new client has been successful, I will update the system to allow the full meter details to be entered into the system. The flow of the system is as follows. Customer into system (agent = non appointment, client = appointment) Record all preliminary meter details Keep a status of all the clients. If successful with client record all meter details. Also allow to record special meter details. An employee can enter any customer into system and also make appointments, for customers. They can also enter agents and agent personnel into the system. I was wandering if someone could help point out weakness in design if I emailed it, or point me to a somewhere with good design solutions. Any help on a more efficient design would be appreciated. I do know I have to change the customer table, to accommodate many address. Thanks Ronnie |
#4
|
|||
|
|||
Ronnie
The folks helping out here are almost all volunteers, so asking a specific question rather than an overall inquiry is more likely to get you a specific response. Please see my questions/comments in-line below: "Ronnie" wrote in message ... Sorry Jeff As there are many tables I could not fit all the info in. To clarify though I have the following tables as they stand, where the 1 donates the 1 side and the M, the many side. Tables Relationships Suppliers 1 Services M Services 1 Sales Details M Sales 1 Sales details M Agent 1 Reps M Rep 1 RepCustomers M Customers 1 RepCustomers M Customers 1 Appointments M Appointments 1 Status M Employee 1 Appointments M Customers 1 Meters M Customers 1 STD M These are simply table names ... and I still don't have a very good idea of what kinds of data you are storing in them. My definition of "Agent" and yours may not match. As I said I know I have to split my customer table to Customer has Many Address’s, I would also like to relate some customer to others, E.G Head Company, etc. Why do you feel you have to split your Customer table? Are you looking for a way to handle the fact that one Customer may have multiple addresses? Can customers also have many phone numbers? The db is based around customers who can have many meters. We're not there, and since this seems to be central, could you describe what kind of meters you are talking about -- meters (units of measure, larger than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage meters), meters (a synonym for 'metrics', items being measured), ...? A customer can be entered into system via an agent who supplies customer leads, or simply by our own leads via an appointment. All meter details must be recorded. I still don't see the connection between customers, agents and meters, let along what meter "details" you are referring to. All customer meters details entered into the system must be processed. Processes? ?Check-off, calculated, sent out for confirmation, ...? Some are processed immediately whereas others will depend on the customer giving you a response. How do you/Access know the difference? Do you handle these two types any differently in Access? If a customer accepts then this is processed as a sale. The sale will be a service from a supplier. I don't understand -- how is a sale a service? The customer can also have some special kind of meters called STD. These are only recorded when this is agreed with the customer as a sale. I thought a sale was a service? An employee can make many appointments as the amount of appointments determines the employee commission, etc. ??What does this mean, and how is it related to the rest? An agent can have many reps, and when a rep gets customer leads, then the customer can belong to any agent/rep. ?? I hope this makes more sense. I'm sure if I were there, and watch what you were trying to model, I'd have a better idea. Perhaps it is only me being dense, but I don't have a clear picture yet. Consider focusing in on a single aspect first, trying something, then posting a specific question. -- Good luck Jeff Boyce Access MVP Thanks Ronnie "Jeff Boyce" wrote: Ronnie Not really enough information to go on... What if I said "I have a vehicle with 3 features but it needs more, what should I do?"? I don't understand the relationships among customers, clients and agents. I don't understand how meters and meter details are involved. I don't understand renewal dates -- what's being renewed, for whom? I don't understand agent personnel. ?!Status?! ?!Many addresses?! I don't understand! -- More info, please ... Jeff Boyce Access MVP "Ronnie" wrote in message ... I have a database which I have constructed which has many tables. It needs redesigning as I don’t think it’s an effective database design, as when trying to achieve tasks its always giving me trouble. The db was originally designed with jus 9 tables. I now have to make amendments to accommodate expansion. What I am trying to get the system to do. The db holds a list of customers. The customers can enter the system via an agent, or via the client making an appointment with a client. Whether the customer enters the system via an agent or via the client, I need to record all the meters details, and renewal dates. I then need to keep a status of the new clients which are coming into the system. When a new client has been successful, I will update the system to allow the full meter details to be entered into the system. The flow of the system is as follows. Customer into system (agent = non appointment, client = appointment) Record all preliminary meter details Keep a status of all the clients. If successful with client record all meter details. Also allow to record special meter details. An employee can enter any customer into system and also make appointments, for customers. They can also enter agents and agent personnel into the system. I was wandering if someone could help point out weakness in design if I emailed it, or point me to a somewhere with good design solutions. Any help on a more efficient design would be appreciated. I do know I have to change the customer table, to accommodate many address. Thanks Ronnie |
#5
|
|||
|
|||
Thanks Jeff
I have gone into more detail so you should get more of an understanding. I know you are volunteers so I appreciate the response. Std STDID Autonumber SalesID Number CustomerID Number STDDay Text STD Night Text Evening Weekend Text Nov-Feb Text Nov- Feb Peak Text Dec-Jan Text Dec-Jan-Peak Text Agents AgentID Autonumber CompanyName Text ContactFirstname Text ContactName Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text Phone Text Phone2 Text Fax Text Home Page Hyperlink Notes Memo AgentReps RepID Autonumber Agent Number Surname Text FirstName Text Mobile Text Notes Memo EmailAddress Text RepCustomers RepCustomerid Autonumber AgentRepID Number CustomerID Number HostRec HostRecid Autonumber HostRecName Text Customers CustomerID Autonumber HostRecID Number CompanyName Text ContactFirstName Text ContactSurname Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text Phone Text Phone2 Text Fax Text HomePage Hyperlink CompanyNumber Text Notes Memo Meters MeterID Autonumber CustomerID Number MeterNumber Text RenewalDate Date/Time Type Text Employee EmployeeID Autonumber Surname Text ChristianName Text Postion Text Title Text Rep Yes/No BirthDate Date/Time StartDate Date/Time Address Text Address2 Text City Text County Text PostCode Text HomePhone Hyperlink Mobile Text Notes Memo EmailAddress Text Appointments Appointmentid Autonumber CustomerID Number DateBooked Date/Time AppointmentWith Text AppointmentTime Date/Time AppointmentDate Date/Time CurrentUsage Text Amount Payable Currency EmployeeID Number SupplierID Number RepSelection Text Status Statusid Autonumber Appointmentid Number Status Text DateCompleted Date/Time Sales SalesID Autonumber Customer Number Date Signed Date/Time SalesDetails SalesID Number Service Number MPAN Text StandingCharge Number EAC Number Day Number Night Number Rate Number HalfHour Yes/No RenewalDate Date/Time MaxCapacity Number WeekdayRate Number Voltage Text LoadFactor Text RenewalStatus Yes/No Service ServiceID Autonumber Supplier Number ServiceName Text Supplier SupplierID Autonumber CompanyName Text ContactName Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text PhoneNo Text Fax Text HomePage Hyperlink A Customer can have multiple addresses, and they can also have more than one phone number. By meters it is referring to an electrical meter or Gas meter, as you would find in a home, or business. Any gas/electrical meter will have a renewal date so you know when to contact customers with renewal price concerning either there gas or electric meters. By processed I mean all new meter details must be entered into the system for a new customer. The customer can then either accept the price of the service you are offering or not accept. If the service is accepted where the service is supplied by a supplier, then a sales record is recorded, so an updated list of all current customers is in the system. If the customer rejects the services you are offering then the renewal date of a meter as shown in the meters table, is important as this informs you when to contact the customer again, and send out renewal invitation. An agent is a kind of outside company who will offer you potential customers for you. The agents will have more than 1 Rep/Personnel working for them. It is possible that different agents will potentially offer you the same customer. This is why I created the table repcustomers. After a rep has offered you a potential customer, then an appointment may be required with the customer. The rep from the agent would have potentially done most of the work, so in most of the customers the rep has informed you about, it would be a case of contacting the customer and see if they want to accept any of the services you are offering. The organisation for whom this database is designed for also have there own employees/reps. The employees will also look for potential customers to sell them a service. When an employee identifies a potential customer, an appointment is generally required to visit the customer. Commission is paid to employee‘s for arranging appointments, so the system need to recognize which employee has made appointments. After an appointment with a customer, then the client may not accept the service immediately or not at all. The system needs to keep a track of customers which a response is required. A customer can also have specials deals on there meters. I have an STD table which show the different type of data e.g. DecJan, from the standard meter details. The sales details table holds all the field which a standard meter would have, such as Load factor, Voltage, Day, Night, etc. The hostrec table I have created is because a customer has to belong to one, and only one hostrec. This is like an area from which the gas or electric service is supplied. Given the relationships I have indicated I was after a more effective design than the one I currently have as I am sure this can be achieved. I know you said try to focus on a single aspect the post a specific question. The overall design of my database is my concern, as opposed to a specific aspect of it. Thanks Ronnie "Jeff Boyce" wrote: Ronnie The folks helping out here are almost all volunteers, so asking a specific question rather than an overall inquiry is more likely to get you a specific response. Please see my questions/comments in-line below: "Ronnie" wrote in message ... Sorry Jeff As there are many tables I could not fit all the info in. To clarify though I have the following tables as they stand, where the 1 donates the 1 side and the M, the many side. Tables Relationships Suppliers 1 Services M Services 1 Sales Details M Sales 1 Sales details M Agent 1 Reps M Rep 1 RepCustomers M Customers 1 RepCustomers M Customers 1 Appointments M Appointments 1 Status M Employee 1 Appointments M Customers 1 Meters M Customers 1 STD M These are simply table names ... and I still don't have a very good idea of what kinds of data you are storing in them. My definition of "Agent" and yours may not match. As I said I know I have to split my customer table to Customer has Many Address’s, I would also like to relate some customer to others, E.G Head Company, etc. Why do you feel you have to split your Customer table? Are you looking for a way to handle the fact that one Customer may have multiple addresses? Can customers also have many phone numbers? The db is based around customers who can have many meters. We're not there, and since this seems to be central, could you describe what kind of meters you are talking about -- meters (units of measure, larger than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage meters), meters (a synonym for 'metrics', items being measured), ...? A customer can be entered into system via an agent who supplies customer leads, or simply by our own leads via an appointment. All meter details must be recorded. I still don't see the connection between customers, agents and meters, let along what meter "details" you are referring to. All customer meters details entered into the system must be processed. Processes? ?Check-off, calculated, sent out for confirmation, ...? Some are processed immediately whereas others will depend on the customer giving you a response. How do you/Access know the difference? Do you handle these two types any differently in Access? If a customer accepts then this is processed as a sale. The sale will be a service from a supplier. I don't understand -- how is a sale a service? The customer can also have some special kind of meters called STD. These are only recorded when this is agreed with the customer as a sale. I thought a sale was a service? An employee can make many appointments as the amount of appointments determines the employee commission, etc. ??What does this mean, and how is it related to the rest? An agent can have many reps, and when a rep gets customer leads, then the customer can belong to any agent/rep. ?? I hope this makes more sense. I'm sure if I were there, and watch what you were trying to model, I'd have a better idea. Perhaps it is only me being dense, but I don't have a clear picture yet. Consider focusing in on a single aspect first, trying something, then posting a specific question. -- Good luck Jeff Boyce Access MVP Thanks Ronnie "Jeff Boyce" wrote: Ronnie Not really enough information to go on... What if I said "I have a vehicle with 3 features but it needs more, what should I do?"? I don't understand the relationships among customers, clients and agents. I don't understand how meters and meter details are involved. I don't understand renewal dates -- what's being renewed, for whom? I don't understand agent personnel. ?!Status?! ?!Many addresses?! I don't understand! -- More info, please ... Jeff Boyce Access MVP "Ronnie" wrote in message ... I have a database which I have constructed which has many tables. It needs redesigning as I don’t think it’s an effective database design, as when trying to achieve tasks its always giving me trouble. The db was originally designed with jus 9 tables. I now have to make amendments to accommodate expansion. What I am trying to get the system to do. The db holds a list of customers. The customers can enter the system via an agent, or via the client making an appointment with a client. Whether the customer enters the system via an agent or via the client, I need to record all the meters details, and renewal dates. I then need to keep a status of the new clients which are coming into the system. When a new client has been successful, I will update the system to allow the full meter details to be entered into the system. The flow of the system is as follows. Customer into system (agent = non appointment, client = appointment) Record all preliminary meter details Keep a status of all the clients. If successful with client record all meter details. Also allow to record special meter details. An employee can enter any customer into system and also make appointments, for customers. They can also enter agents and agent personnel into the system. I was wandering if someone could help point out weakness in design if I emailed it, or point me to a somewhere with good design solutions. Any help on a more efficient design would be appreciated. I do know I have to change the customer table, to accommodate many address. Thanks Ronnie |
#6
|
|||
|
|||
Ronnie
Thanks for the clarifications. I'll stand on my recommendation... narrow down your focus to a single aspect of what you are trying to do, and start a new thread on that one topic. As you have laid it out, it sounds like you are seeking a comprehensive review of your system/design. This is not something I have time to do as a volunteer, if I am to also help other folks posting in the groups. But perhaps one of the other 'group readers has time to?! Good luck on your project Jeff Boyce Access MVP "Ronnie" wrote in message ... Thanks Jeff I have gone into more detail so you should get more of an understanding. I know you are volunteers so I appreciate the response. Std STDID Autonumber SalesID Number CustomerID Number STDDay Text STD Night Text Evening Weekend Text Nov-Feb Text Nov- Feb Peak Text Dec-Jan Text Dec-Jan-Peak Text Agents AgentID Autonumber CompanyName Text ContactFirstname Text ContactName Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text Phone Text Phone2 Text Fax Text Home Page Hyperlink Notes Memo AgentReps RepID Autonumber Agent Number Surname Text FirstName Text Mobile Text Notes Memo EmailAddress Text RepCustomers RepCustomerid Autonumber AgentRepID Number CustomerID Number HostRec HostRecid Autonumber HostRecName Text Customers CustomerID Autonumber HostRecID Number CompanyName Text ContactFirstName Text ContactSurname Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text Phone Text Phone2 Text Fax Text HomePage Hyperlink CompanyNumber Text Notes Memo Meters MeterID Autonumber CustomerID Number MeterNumber Text RenewalDate Date/Time Type Text Employee EmployeeID Autonumber Surname Text ChristianName Text Postion Text Title Text Rep Yes/No BirthDate Date/Time StartDate Date/Time Address Text Address2 Text City Text County Text PostCode Text HomePhone Hyperlink Mobile Text Notes Memo EmailAddress Text Appointments Appointmentid Autonumber CustomerID Number DateBooked Date/Time AppointmentWith Text AppointmentTime Date/Time AppointmentDate Date/Time CurrentUsage Text Amount Payable Currency EmployeeID Number SupplierID Number RepSelection Text Status Statusid Autonumber Appointmentid Number Status Text DateCompleted Date/Time Sales SalesID Autonumber Customer Number Date Signed Date/Time SalesDetails SalesID Number Service Number MPAN Text StandingCharge Number EAC Number Day Number Night Number Rate Number HalfHour Yes/No RenewalDate Date/Time MaxCapacity Number WeekdayRate Number Voltage Text LoadFactor Text RenewalStatus Yes/No Service ServiceID Autonumber Supplier Number ServiceName Text Supplier SupplierID Autonumber CompanyName Text ContactName Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text PhoneNo Text Fax Text HomePage Hyperlink A Customer can have multiple addresses, and they can also have more than one phone number. By meters it is referring to an electrical meter or Gas meter, as you would find in a home, or business. Any gas/electrical meter will have a renewal date so you know when to contact customers with renewal price concerning either there gas or electric meters. By processed I mean all new meter details must be entered into the system for a new customer. The customer can then either accept the price of the service you are offering or not accept. If the service is accepted where the service is supplied by a supplier, then a sales record is recorded, so an updated list of all current customers is in the system. If the customer rejects the services you are offering then the renewal date of a meter as shown in the meters table, is important as this informs you when to contact the customer again, and send out renewal invitation. An agent is a kind of outside company who will offer you potential customers for you. The agents will have more than 1 Rep/Personnel working for them. It is possible that different agents will potentially offer you the same customer. This is why I created the table repcustomers. After a rep has offered you a potential customer, then an appointment may be required with the customer. The rep from the agent would have potentially done most of the work, so in most of the customers the rep has informed you about, it would be a case of contacting the customer and see if they want to accept any of the services you are offering. The organisation for whom this database is designed for also have there own employees/reps. The employees will also look for potential customers to sell them a service. When an employee identifies a potential customer, an appointment is generally required to visit the customer. Commission is paid to employee‘s for arranging appointments, so the system need to recognize which employee has made appointments. After an appointment with a customer, then the client may not accept the service immediately or not at all. The system needs to keep a track of customers which a response is required. A customer can also have specials deals on there meters. I have an STD table which show the different type of data e.g. DecJan, from the standard meter details. The sales details table holds all the field which a standard meter would have, such as Load factor, Voltage, Day, Night, etc. The hostrec table I have created is because a customer has to belong to one, and only one hostrec. This is like an area from which the gas or electric service is supplied. Given the relationships I have indicated I was after a more effective design than the one I currently have as I am sure this can be achieved. I know you said try to focus on a single aspect the post a specific question. The overall design of my database is my concern, as opposed to a specific aspect of it. Thanks Ronnie "Jeff Boyce" wrote: Ronnie The folks helping out here are almost all volunteers, so asking a specific question rather than an overall inquiry is more likely to get you a specific response. Please see my questions/comments in-line below: "Ronnie" wrote in message ... Sorry Jeff As there are many tables I could not fit all the info in. To clarify though I have the following tables as they stand, where the 1 donates the 1 side and the M, the many side. Tables Relationships Suppliers 1 Services M Services 1 Sales Details M Sales 1 Sales details M Agent 1 Reps M Rep 1 RepCustomers M Customers 1 RepCustomers M Customers 1 Appointments M Appointments 1 Status M Employee 1 Appointments M Customers 1 Meters M Customers 1 STD M These are simply table names ... and I still don't have a very good idea of what kinds of data you are storing in them. My definition of "Agent" and yours may not match. As I said I know I have to split my customer table to Customer has Many Address’s, I would also like to relate some customer to others, E.G Head Company, etc. Why do you feel you have to split your Customer table? Are you looking for a way to handle the fact that one Customer may have multiple addresses? Can customers also have many phone numbers? The db is based around customers who can have many meters. We're not there, and since this seems to be central, could you describe what kind of meters you are talking about -- meters (units of measure, larger than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage meters), meters (a synonym for 'metrics', items being measured), ...? A customer can be entered into system via an agent who supplies customer leads, or simply by our own leads via an appointment. All meter details must be recorded. I still don't see the connection between customers, agents and meters, let along what meter "details" you are referring to. All customer meters details entered into the system must be processed. Processes? ?Check-off, calculated, sent out for confirmation, ...? Some are processed immediately whereas others will depend on the customer giving you a response. How do you/Access know the difference? Do you handle these two types any differently in Access? If a customer accepts then this is processed as a sale. The sale will be a service from a supplier. I don't understand -- how is a sale a service? The customer can also have some special kind of meters called STD. These are only recorded when this is agreed with the customer as a sale. I thought a sale was a service? An employee can make many appointments as the amount of appointments determines the employee commission, etc. ??What does this mean, and how is it related to the rest? An agent can have many reps, and when a rep gets customer leads, then the customer can belong to any agent/rep. ?? I hope this makes more sense. I'm sure if I were there, and watch what you were trying to model, I'd have a better idea. Perhaps it is only me being dense, but I don't have a clear picture yet. Consider focusing in on a single aspect first, trying something, then posting a specific question. -- Good luck Jeff Boyce Access MVP Thanks Ronnie "Jeff Boyce" wrote: Ronnie Not really enough information to go on... What if I said "I have a vehicle with 3 features but it needs more, what should I do?"? I don't understand the relationships among customers, clients and agents. I don't understand how meters and meter details are involved. I don't understand renewal dates -- what's being renewed, for whom? I don't understand agent personnel. ?!Status?! ?!Many addresses?! I don't understand! -- More info, please ... Jeff Boyce Access MVP "Ronnie" wrote in message ... I have a database which I have constructed which has many tables. It needs redesigning as I don’t think it’s an effective database design, as when trying to achieve tasks its always giving me trouble. The db was originally designed with jus 9 tables. I now have to make amendments to accommodate expansion. What I am trying to get the system to do. The db holds a list of customers. The customers can enter the system via an agent, or via the client making an appointment with a client. Whether the customer enters the system via an agent or via the client, I need to record all the meters details, and renewal dates. I then need to keep a status of the new clients which are coming into the system. When a new client has been successful, I will update the system to allow the full meter details to be entered into the system. The flow of the system is as follows. Customer into system (agent = non appointment, client = appointment) Record all preliminary meter details Keep a status of all the clients. If successful with client record all meter details. Also allow to record special meter details. An employee can enter any customer into system and also make appointments, for customers. They can also enter agents and agent personnel into the system. I was wandering if someone could help point out weakness in design if I emailed it, or point me to a somewhere with good design solutions. Any help on a more efficient design would be appreciated. I do know I have to change the customer table, to accommodate many address. Thanks Ronnie |
#7
|
|||
|
|||
Thanks Jeff.
It is probably at a lot to ask, however there was no harm in trying "Jeff Boyce" wrote: Ronnie Thanks for the clarifications. I'll stand on my recommendation... narrow down your focus to a single aspect of what you are trying to do, and start a new thread on that one topic. As you have laid it out, it sounds like you are seeking a comprehensive review of your system/design. This is not something I have time to do as a volunteer, if I am to also help other folks posting in the groups. But perhaps one of the other 'group readers has time to?! Good luck on your project Jeff Boyce Access MVP "Ronnie" wrote in message ... Thanks Jeff I have gone into more detail so you should get more of an understanding. I know you are volunteers so I appreciate the response. Std STDID Autonumber SalesID Number CustomerID Number STDDay Text STD Night Text Evening Weekend Text Nov-Feb Text Nov- Feb Peak Text Dec-Jan Text Dec-Jan-Peak Text Agents AgentID Autonumber CompanyName Text ContactFirstname Text ContactName Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text Phone Text Phone2 Text Fax Text Home Page Hyperlink Notes Memo AgentReps RepID Autonumber Agent Number Surname Text FirstName Text Mobile Text Notes Memo EmailAddress Text RepCustomers RepCustomerid Autonumber AgentRepID Number CustomerID Number HostRec HostRecid Autonumber HostRecName Text Customers CustomerID Autonumber HostRecID Number CompanyName Text ContactFirstName Text ContactSurname Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text Phone Text Phone2 Text Fax Text HomePage Hyperlink CompanyNumber Text Notes Memo Meters MeterID Autonumber CustomerID Number MeterNumber Text RenewalDate Date/Time Type Text Employee EmployeeID Autonumber Surname Text ChristianName Text Postion Text Title Text Rep Yes/No BirthDate Date/Time StartDate Date/Time Address Text Address2 Text City Text County Text PostCode Text HomePhone Hyperlink Mobile Text Notes Memo EmailAddress Text Appointments Appointmentid Autonumber CustomerID Number DateBooked Date/Time AppointmentWith Text AppointmentTime Date/Time AppointmentDate Date/Time CurrentUsage Text Amount Payable Currency EmployeeID Number SupplierID Number RepSelection Text Status Statusid Autonumber Appointmentid Number Status Text DateCompleted Date/Time Sales SalesID Autonumber Customer Number Date Signed Date/Time SalesDetails SalesID Number Service Number MPAN Text StandingCharge Number EAC Number Day Number Night Number Rate Number HalfHour Yes/No RenewalDate Date/Time MaxCapacity Number WeekdayRate Number Voltage Text LoadFactor Text RenewalStatus Yes/No Service ServiceID Autonumber Supplier Number ServiceName Text Supplier SupplierID Autonumber CompanyName Text ContactName Text ContactTitle Text Address Text Address2 Text City Text County Text PostCode Text PhoneNo Text Fax Text HomePage Hyperlink A Customer can have multiple addresses, and they can also have more than one phone number. By meters it is referring to an electrical meter or Gas meter, as you would find in a home, or business. Any gas/electrical meter will have a renewal date so you know when to contact customers with renewal price concerning either there gas or electric meters. By processed I mean all new meter details must be entered into the system for a new customer. The customer can then either accept the price of the service you are offering or not accept. If the service is accepted where the service is supplied by a supplier, then a sales record is recorded, so an updated list of all current customers is in the system. If the customer rejects the services you are offering then the renewal date of a meter as shown in the meters table, is important as this informs you when to contact the customer again, and send out renewal invitation. An agent is a kind of outside company who will offer you potential customers for you. The agents will have more than 1 Rep/Personnel working for them. It is possible that different agents will potentially offer you the same customer. This is why I created the table repcustomers. After a rep has offered you a potential customer, then an appointment may be required with the customer. The rep from the agent would have potentially done most of the work, so in most of the customers the rep has informed you about, it would be a case of contacting the customer and see if they want to accept any of the services you are offering. The organisation for whom this database is designed for also have there own employees/reps. The employees will also look for potential customers to sell them a service. When an employee identifies a potential customer, an appointment is generally required to visit the customer. Commission is paid to employee‘s for arranging appointments, so the system need to recognize which employee has made appointments. After an appointment with a customer, then the client may not accept the service immediately or not at all. The system needs to keep a track of customers which a response is required. A customer can also have specials deals on there meters. I have an STD table which show the different type of data e.g. DecJan, from the standard meter details. The sales details table holds all the field which a standard meter would have, such as Load factor, Voltage, Day, Night, etc. The hostrec table I have created is because a customer has to belong to one, and only one hostrec. This is like an area from which the gas or electric service is supplied. Given the relationships I have indicated I was after a more effective design than the one I currently have as I am sure this can be achieved. I know you said try to focus on a single aspect the post a specific question. The overall design of my database is my concern, as opposed to a specific aspect of it. Thanks Ronnie "Jeff Boyce" wrote: Ronnie The folks helping out here are almost all volunteers, so asking a specific question rather than an overall inquiry is more likely to get you a specific response. Please see my questions/comments in-line below: "Ronnie" wrote in message ... Sorry Jeff As there are many tables I could not fit all the info in. To clarify though I have the following tables as they stand, where the 1 donates the 1 side and the M, the many side. Tables Relationships Suppliers 1 Services M Services 1 Sales Details M Sales 1 Sales details M Agent 1 Reps M Rep 1 RepCustomers M Customers 1 RepCustomers M Customers 1 Appointments M Appointments 1 Status M Employee 1 Appointments M Customers 1 Meters M Customers 1 STD M These are simply table names ... and I still don't have a very good idea of what kinds of data you are storing in them. My definition of "Agent" and yours may not match. As I said I know I have to split my customer table to Customer has Many Address’s, I would also like to relate some customer to others, E.G Head Company, etc. Why do you feel you have to split your Customer table? Are you looking for a way to handle the fact that one Customer may have multiple addresses? Can customers also have many phone numbers? The db is based around customers who can have many meters. We're not there, and since this seems to be central, could you describe what kind of meters you are talking about -- meters (units of measure, larger than a 'yard'), meters (Volt-Ohm meters), meters (electrical usage meters), meters (a synonym for 'metrics', items being measured), ...? A customer can be entered into system via an agent who supplies customer leads, or simply by our own leads via an appointment. All meter details must be recorded. I still don't see the connection between customers, agents and meters, let along what meter "details" you are referring to. All customer meters details entered into the system must be processed. Processes? ?Check-off, calculated, sent out for confirmation, ...? Some are processed immediately whereas others will depend on the customer giving you a response. How do you/Access know the difference? Do you handle these two types any differently in Access? If a customer accepts then this is processed as a sale. The sale will be a service from a supplier. I don't understand -- how is a sale a service? The customer can also have some special kind of meters called STD. These are only recorded when this is agreed with the customer as a sale. I thought a sale was a service? An employee can make many appointments as the amount of appointments determines the employee commission, etc. ??What does this mean, and how is it related to the rest? An agent can have many reps, and when a rep gets customer leads, then the customer can belong to any agent/rep. ?? I hope this makes more sense. I'm sure if I were there, and watch what you were trying to model, I'd have a better idea. Perhaps it is only me being dense, but I don't have a clear picture yet. Consider focusing in on a single aspect first, trying something, then posting a specific question. -- Good luck Jeff Boyce Access MVP Thanks Ronnie "Jeff Boyce" wrote: Ronnie Not really enough information to go on... What if I said "I have a vehicle with 3 features but it needs more, what should I do?"? I don't understand the relationships among customers, clients and agents. I don't understand how meters and meter details are involved. I don't understand renewal dates -- what's being renewed, for whom? I don't understand agent personnel. ?!Status?! ?!Many addresses?! I don't understand! -- More info, please ... Jeff Boyce Access MVP "Ronnie" wrote in message ... I have a database which I have constructed which has many tables. It needs redesigning as I don’t think it’s an effective database design, as when trying to achieve tasks its always giving me trouble. The db was originally designed with jus 9 tables. I now have to make amendments to accommodate expansion. What I am trying to get the system to do. The db holds a list of customers. The customers can enter the system via an agent, or via the client making an appointment with a client. Whether the customer enters the system via an agent or via the client, I need to record all the meters details, and renewal dates. I then need to keep a status of the new clients which are coming into the system. When a new client has been successful, I will update the system to allow the full meter details to be entered into the system. The flow of the system is as follows. Customer into system (agent = non appointment, client = appointment) Record all preliminary meter details Keep a status of all the clients. If successful with client record all meter details. Also allow to record special meter details. An employee can enter any customer into system and also make appointments, for customers. They can also enter agents and agent personnel into the system. I was wandering if someone could help point out weakness in design if I emailed it, or point me to a somewhere with good design solutions. Any help on a more efficient design would be appreciated. I do know I have to change the customer table, to accommodate many address. Thanks Ronnie |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Who owns the copyright on graphic design layouts prepared in MS Wo | Karen | General Discussion | 4 | February 1st, 2005 07:01 AM |
How can I customise an entire design set | David | Publisher | 2 | November 12th, 2004 09:43 AM |
design master problem | J. Vermeer | General Discussion | 0 | September 8th, 2004 03:23 PM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
opening a document so it is NOT in design mode | Brad Pears | New Users | 1 | May 3rd, 2004 09:13 PM |