Linking table where both columns are the same field 1

[snoopy75]

Hello all,
I'm developing a parts database, and I need to store information about which parts mate together. The only way I can think of to do it is to create a linking table with two similar columns, like:
[tt]
Part1 Part2
----- -----
11478 11340
11478 85675
11478 11415
11229 15667
[/tt]
and so on. But it seems like there would be some confusion if entries like this got in there:
[tt]
11478 11340
11340 11478
[/tt]
How do I avoid duplication of mates in such a table? Or is there a better way to store such information altogether?

 

[lynchg]

Is there any hierarchy to the parts?

In other words, I would gues that the relationship of 11478 to 11340 is not the same as the relationship of 11340 to 11478. If 11340 fits into 11478, then 11478 couldn't fit into 11340.

See if there are natural hierarchies like that amongst the parts. It is also likely that one particular part will fit into multiple "parent" parts, and there will be many possible combinations. This would indicate a many-to-many relationship amongst the parts which would necessitate 3 tables (Parent Parts, Child Parts, and a parts junction table holding one record for every possible combination). It is also possible that a child of one part could be a parent of another and it could appear in both part tables.

I am doing a lot of speculating, maybe some of this makes sense to you and might give you ideas for how you want to store your data.

HTH

 

[snoopy75]

Yes, there is a hierarchy, but I think I've already got that covered in a NextHigherAssembly field in my Parts table. Right now I'm only worried about parts that are on an 'equal level' in the hierarchy, and which ones mate directly with which other ones. And I'm not worried about how they fit together, only that they fit together.

Thanks for the ideas, though. You've got my brain-wheels churning.

 

[PHV]

You may try to add a Table level validation rule saying that Part2 must be greater than Part1.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[snoopy75]

Hey, so simple... and yet it just might work. Thanks, PHV!