Relationship Issue

  • Thread starter Thread starter Jason Lepack
  • Start date Start date
J

Jason Lepack

tbl_parts:
part_id - autonumber - pk
part_description - text

tbl_bom: (bill of material)
bom_id - autonumber - indexed(no dupes)
part_id - number - PK
sub_part_id - number - PK

In this situation how would I create the relationships between the two
tables? I need to enforce referential integrity on both part_id and
sub_part_id in tbl_bom to part_id in tbl_parts.

Is it acceptable to load tbl_parts into the relationship window twice
and link the two fields to the seperate instances? Are there any
unforseen issues with this method?

Thanks In Advance,
Jason Lepack
 
tbl_parts:
part_id - autonumber - pk
part_description - text

tbl_bom: (bill of material)
bom_id - autonumber - indexed(no dupes)
part_id - number - PK
sub_part_id - number - PK
Click to expand...

Yes, your structure is denormalized. That's the nature of the
adjacency list approach :(

If you want a normalized model, try the nested sets approach. See:

Trees in SQL
by Joe Celko
http://www.intelligententerprise.com/001020/celko.jhtml

His book, 'Trees and Hierarchies in SQL for Smarties' is pretty much
essential reading on the subject.

Jamie.

--
 
Interesting... Good note Jamie.

Goes back and pulls out his University text on Foliage... I mean
trees.

Cheers,
Jason Lepack
 
Pardon my ignorance for a moment.

I looked at this example that you pointed me to and have a question.
When I add a new item do I not have to modify every record in the
tree? That sounds like a lot of work. Granted I looked at my
original structure and decided that querying it would be quite
disgusting. But the question still exists.

Thanks,
Jason
 
I looked at this example that you pointed me to and have a question.
When I add a new item do I not have to modify every record in the
tree? That sounds like a lot of work. Granted I looked at my
original structure and decided that querying it would be quite
disgusting. But the question still exists.
Click to expand...

Yes, it's a simple example that merely scratches the surface. Get the
book! Here's a hint, though: the example uses sequenced integers for
lft and rgt, however the full value range for integer should allow
gaps (spread) to be large enough for them to be subdivided many times.
Which do you anticipate doing more frequently: querying data or
performing INSERTs and DELETEs? For infrequent insertion models
(consider in a typical organisation the personnel changes more
frequently than the reporting structure), it may be easier to open
gaps, reorganise the model, etc off line and the book provides ways of
achieving such operations.

Although the book favours the nested sets approach, there are also
hints, tips, SQL code, etc to get the most out of the adjacency list
approach.

Jamie.

--
 
Ok, so I'm over the fact that I would possibly have to update
potentially over 90% of my recordset every time I add a new part
(depending on how far to the left of the tree I insert into), but I
have another question.

I'm going to end up with the same part (which is built from sub_parts)
occurring in multiple places within this tree. Am I going to have to
include them all each time?

Adjacency Model:
bom_id, part_id, sub_part_id
1, 1, 2
2, 1, 3
3, 3, 4
4, 5, 3

Based on the above model it contains this structure:
1
2
3
4
5
3
4

Tree Model:
part_id, lft, rgt
0, 1, 16 - root of tree
1, 2, 9
2, 3, 4
3, 5, 8
4, 6, 7
5, 10, 15
3, 11, 14
4, 12, 13

I'm not too worried about the addition of the root of tree nodes and
the individual nodes added for the master parts (1, 5) but I'm greatly
disturbed by the fact that I would have to add the extra records when
a part is required in two seperate parts. Is this the case or is
there a way to work around this?

Cheers,
Jason Lepack
 
Back
Top