Database - Table Set Up

[eeboy]

I need some help in setting up the tables in my database. I have a dataset of 'Assemblies' and a dataset of 'Parts' which make up the Assemblies. My confusion comes from the fact that a given part can be linked to multiple assemblies. So, the problem is no longer one to many... it's more many to many.

Is there a simple solution to this problem?

Thanks!

[NigelQ]

Whenever you think there's a solution to something like this, there generally is. You just need to take a big tug of coffee and think it through.

Instead of thinking about it from the part's perspective, think about it from the assembly's. If that doesn't work, think outside the box a little...

Ok, so an assembly can have a bunch of parts I'm guessing, so why not create a new lookup table that cross references the parts associated with an assembly. Basically there would be two columns: AssemblyID and PartID

Whenever you want to know which parts are in an assembly, just do a select from the cross reference table:

SELECT PartID FROM tblCrossRef WHERE AssemblyID = 1234

This should list all of the part ID's for you.

Obviously you could do the same in reverse if you wanted to find which assemblies used certain parts.

Before all of the DBA's jump in here, I should point out that you should probably have a unique column ID in this cross reference table (possibly an autonum) to avoid any future confusion (say when you have the same part used twice in an assembly).

Hope this helps,

- Nigel