Please help me with my Table Design

[Sly600rr]

Okay, I am trying to create a database for my company, this is going to be a very important database that will be used for a long time to come, so I want to make sure I start this thing with the best design possible.

I am by no means an expert with databases, but I am fairly competent. I can usually figure out how to do most things that I want, but I'm struggling at the moment with how to accomplish what in my head seems so simple.


1.) Basically, I need to be able to create a new Part Number.
2.) Be able to add Multiple Standard Processes for each Part Number.
3.) Then add Multiple Part Specific Dimensions and select which process or processes the Dimensions should be Checked at.

So, in a sense, something like this.

The most simple way I can think to do this is to add fields in the PartNumber table like Process1, process2, process3, ect.
Then check-box fields on the Dimension table for which process or processes that dimension is checked at.

But I don't want to limit myself to a set amount of processes...



I'm mostly struggling with two things.. Table design (whether to do, 2 Many-to-Many relationships, or multiple foreign keys) and how to generate the process selection for the dimensions based on which processes are added to the PartNumber


Here is the table design that makes the most since in my head, and that I can "sort of" make work, but I'm worried about referential integrity.

This is the one that seems correct from everything I read. but I struggle to find a way to actually make it work..

I think I really just need a little guidance. I'm probably in over my head, but then again; I always love a good challenge.

Any help or advice would be much appropriated.

Thanks guys.

 

[jdraw]

I provided some links to videos and documents related to database design at
http://www.access-programmers.co.uk/forums/showthread.php?t=221683

I think these would be helpful to you.

Good luck with your project.

 

[gemma-the-husky]

it is hard to tell without understanding your system. you are 100% right in thinking that the table design needs to be correct, though.

I am concerned about seeing standalone tables described as "operation" and "dimensions", as they rarely indicate a "real world object"

generally speaking your tables will represent real-world objects, and the actions you subject them to will represent a real-world process.

you seem to have tables representing processes, rather than the real-world entities.

often, all you might need in the part number table IS a process field to indicate the process stage it has reached. you may need several such fields. it depends whether the processes are mutually exclusive or not.

maybe it is worth getting some professional help in the data analysis, as it will make your subsequent work far easier.