Relationship Issue

[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

 

[Roger Carlson]

Yes, that is exactly the way to create two separate relationships between
two tables. The second instance will be called tbl_parts_1, but Access will
reference the correct table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jason Lepack]

Thanks Roger.

Yes, that is exactly the way to create two separate relationships between
two tables. The second instance will be called tbl_parts_1, but Access will
reference the correct table.

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L










- Show quoted text -

 

[Jamie Collins]

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

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.

--

 

[Jason Lepack]

Interesting... Good note Jamie.

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

Cheers,
Jason Lepack

 

[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

 

[Jamie Collins]

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.

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.

--

 

[Jason Lepack]

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