stardustvega
Member
- Local time
- Today, 05:07
- Joined
- Feb 4, 2022
- Messages
- 36
I'm working on restructuring an Access database at work, and I'm curious if there's a 'best practice' in this scenario.
In this database, there's a table of customers and a table of reps.
All active customers get listed in our directory with a primary rep. There can only ever be one directory rep per company.
What I'm wondering is if it's better practice to keep all the directory info in the customers table, like this:
TblCustomers:
Or whether it'd be better to put the directory info into its own table with a one-to-one relationship to the customers table, like this:
TblCustomers:
TblDirectoryInfo:
If I understand correctly, both options would comply with at least 1st and 2nd normalization. Here's my thoughts on the pros and cons of each:
Option 1 (keep it all in TblCustomers):
1) Pro: Makes queries simple. I won't need a join to retrieve directory data.
2) Con: Adds to the size of the TbleCustomer. Makes it a little harder to read.
3) Con: Most queries that use TblCustomers won't use the directory info data, which is only used when we create the directory once a year.
Option 2 (split it into two tables):
1) Pro: More compact and readable tables.
2) Pro: Keeps only the most regularly used info in TblCustomers.
3) Con: A join will have to be used any time that we do need directory info, including in the main data entry form (since users will select a rep to be the directory rep).
In this database, there's a table of customers and a table of reps.
All active customers get listed in our directory with a primary rep. There can only ever be one directory rep per company.
What I'm wondering is if it's better practice to keep all the directory info in the customers table, like this:
TblCustomers:
Customer_ID | Customer_Name | Dir_Rep_ID | Dir_Show_Email | Dir_Show_Cell |
1 | Sample Co | 23 | TRUE | TRUE |
2 | Lorem, Inc | 43 | FALSE | TRUE |
3 | Ipsum LLC | 72 | FALSE | FALSE |
Or whether it'd be better to put the directory info into its own table with a one-to-one relationship to the customers table, like this:
TblCustomers:
Customer_ID | Customer_Name | Dir_Info_ID |
1 | Sample Co | 3 |
2 | Lorem, Inc | 4 |
3 | Ipsum LLC | 5 |
TblDirectoryInfo:
Dir_Info_ID | Rep_ID | Dir_Show_Email | Dir_Show_Cell |
3 | 23 | TRUE | TRUE |
4 | 43 | FALSE | TRUE |
5 | 72 | FALSE | FALSE |
If I understand correctly, both options would comply with at least 1st and 2nd normalization. Here's my thoughts on the pros and cons of each:
Option 1 (keep it all in TblCustomers):
1) Pro: Makes queries simple. I won't need a join to retrieve directory data.
2) Con: Adds to the size of the TbleCustomer. Makes it a little harder to read.
3) Con: Most queries that use TblCustomers won't use the directory info data, which is only used when we create the directory once a year.
Option 2 (split it into two tables):
1) Pro: More compact and readable tables.
2) Pro: Keeps only the most regularly used info in TblCustomers.
3) Con: A join will have to be used any time that we do need directory info, including in the main data entry form (since users will select a rep to be the directory rep).