omniaccess
Technical User
I have a relatively complex project of clients, suppliers, employees, manufacturers, contractors, etc.
I have fully developed the client area of my program by having a table with only about 15 or 20 fields for each client. These fields are enough info to identify who they are, such as client type, last name, first name, other name, business name, when created, created by, changed date, and changed by. For all the other information about each client there are multiple tables in one-to-many relationships so that each client can have multiple addresses, contact members, contact info, dates, schedules, etc. It has been working quite well, even though I have had to watch out so I do not connect too much information together at the same time and slow down the system (in other words, too many tabbed pages with connected subforms all active at the same time).
I am now at the point of adding Suppliers, Manufacturers, Employees, and contractors to the equation. I refer to all of these people as resource entities for the business.
Here is my question: Should I put all of the resource people in their own table and keep them separate from the Client table, or should I use the existing structure to put all entities in the same table with my clients. I am not sure which arrangement for tables would make the most sense for building for the future. This layout could be used for much larger systems in the future.
Several other factors: (1) in my scheduling section of the program, I have made it so you can schedule more than clients, such as office tasks, communication, etc. In the scheduling, I was thinking it would be nice to be able to select a supplier (in the "who" field of the schedule item) to order parts. Or schedule an employee if scheduling an employee review. Or selecting a manufacturer if an appointment is set to meet with that company rather than working with clients. This would be flexible, but would make sense if the resource people were all in the same table as the clients.
(2) It would aid in global searching for anyone because all entities would all be in the same table.
(3) Management would be easier because they would all be in one table and whatever resources they have would simply be attached through other tables and subforms specific to their entity type (One form has attached subforms that relate to clients, another form has subforms and tables related to manufacturers, etc.).
(4) They would all have global reference ID separate from each other because none would get assigned the same ID if they are all in the same table.
The immediate drawback I can think of is that the entity table will get larger than if they are separate. At this point, the client list has nearly 10,000 clients in it, and I can imagine the manufacturers and suppliers also getting up to 10,000 entries in it.
Another drawback is that anyone who does power searches on the data would always have to remember to specify which entity type they are, whether a client, manufacturer, Supplier, etc.
One other drawback would be in selecting an entity from a combo box. It would probably work to put "sort by" or "show Type" selections near the combo box to sort or filter the combo box to quickly show them only the entity type they want.
Currently, the program will be running on one or just a few computers at a time. It is possible it will get scaled up to a SQL server backend for larger installments.
I have seen many programmers layout a separate table for each entity type (Client, Manufacturer, Supplier, Employee, Contractor, etc) but I would rather handle their differences though related tables and subforms applicable for each entity type.
I would appreciate anyone's input who have considered this issue or who have dealt with tables with these type of numbers and have seen positives and negatives to their layout. Thank you
I have fully developed the client area of my program by having a table with only about 15 or 20 fields for each client. These fields are enough info to identify who they are, such as client type, last name, first name, other name, business name, when created, created by, changed date, and changed by. For all the other information about each client there are multiple tables in one-to-many relationships so that each client can have multiple addresses, contact members, contact info, dates, schedules, etc. It has been working quite well, even though I have had to watch out so I do not connect too much information together at the same time and slow down the system (in other words, too many tabbed pages with connected subforms all active at the same time).
I am now at the point of adding Suppliers, Manufacturers, Employees, and contractors to the equation. I refer to all of these people as resource entities for the business.
Here is my question: Should I put all of the resource people in their own table and keep them separate from the Client table, or should I use the existing structure to put all entities in the same table with my clients. I am not sure which arrangement for tables would make the most sense for building for the future. This layout could be used for much larger systems in the future.
Several other factors: (1) in my scheduling section of the program, I have made it so you can schedule more than clients, such as office tasks, communication, etc. In the scheduling, I was thinking it would be nice to be able to select a supplier (in the "who" field of the schedule item) to order parts. Or schedule an employee if scheduling an employee review. Or selecting a manufacturer if an appointment is set to meet with that company rather than working with clients. This would be flexible, but would make sense if the resource people were all in the same table as the clients.
(2) It would aid in global searching for anyone because all entities would all be in the same table.
(3) Management would be easier because they would all be in one table and whatever resources they have would simply be attached through other tables and subforms specific to their entity type (One form has attached subforms that relate to clients, another form has subforms and tables related to manufacturers, etc.).
(4) They would all have global reference ID separate from each other because none would get assigned the same ID if they are all in the same table.
The immediate drawback I can think of is that the entity table will get larger than if they are separate. At this point, the client list has nearly 10,000 clients in it, and I can imagine the manufacturers and suppliers also getting up to 10,000 entries in it.
Another drawback is that anyone who does power searches on the data would always have to remember to specify which entity type they are, whether a client, manufacturer, Supplier, etc.
One other drawback would be in selecting an entity from a combo box. It would probably work to put "sort by" or "show Type" selections near the combo box to sort or filter the combo box to quickly show them only the entity type they want.
Currently, the program will be running on one or just a few computers at a time. It is possible it will get scaled up to a SQL server backend for larger installments.
I have seen many programmers layout a separate table for each entity type (Client, Manufacturer, Supplier, Employee, Contractor, etc) but I would rather handle their differences though related tables and subforms applicable for each entity type.
I would appreciate anyone's input who have considered this issue or who have dealt with tables with these type of numbers and have seen positives and negatives to their layout. Thank you
