Table design (1 Viewer)

PWG

Registered User.
Local time
Today, 06:52
Joined
Jun 13, 2019
Messages
56
After months of redesigning a Microsoft template to do what i wanted. With the help of to great people here I found out that the table design was no good and that i would continue to have many problems.
So I have started from scratch.Please find attached my tables and relationships for any suggestions or advise on any issues you can see. All comments would be greatly appreciated.
It will be a purchasing inventory data base.
 

Attachments

  • STORE1.accdb
    848 KB · Views: 102

jdraw

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Jan 23, 2006
Messages
15,364
PWG,

Nobody can comment on your tables and relationships without knowledge of your business. Tables and relationships reflect the business entities and attributes and the rules/facts of the business. It all starts with a clear description of the business processes and related rules.

eg. A Product may be obtained from 1 or Many Suppliers...

Some info on Business rules.

An overview of Business Requirements Modelling.

An approach to database design from Barry Williams' site.

Good luck with your project.
 

isladogs

MVP / VIP
Local time
Today, 13:52
Joined
Jan 14, 2017
Messages
18,186
Agree with jdraw though I did advise in your previous thread.

Two general comments.
You have 8 tables with just a primary key and one other field. Some of those tables may well be superfluous.
Your relationships window has too many links and some tables may possibly end up as read only. In general there should only be one route to any table in the relationships window. Many of your tables can be reached in multiple ways.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Feb 19, 2002
Messages
42,981
Naming primary keys "ID" is non-helpful. If you remember the old Bob Newhart show where his neighbors are three brothers all named Darryl, it was great as a joke but it's not so funny when someone new tries to understand your schema.

Also,
1. isn't model a child of Make? The Make table would have two fields - MakeID and Make. The Model table would be - ModelID, Model, and MakeID as the FK to the Make table. Then in Machines only ModelID would be stored rather than both fields. Using cars as the example, you could have a Chevvy Mustang with your current design.
2. Are the various status' really different so that you need separate tables?
3. For the junction tables such as EmployeePrivleges, you need a compound unique index on the two foreign keys. It doesn't make sense for Emp1 to have two records for privledge1 and you need the unique index to prevent that. For junction tables that have no child tables, I don't use an autonumber as the PK since there is no reason to have two unique keys. It's not wrong to include an autonumber and many do it for consistency but you really need the unique index on the two FK's in order to enforce business rules.
 

Users who are viewing this thread

Top Bottom