Relationships

[Roger Bell]

Could some one please advise if there are any implications in having a one to
many relationship in the following situation, ie a Primary key having more
than 1 relationship to separate tables
Item Table-PK ItemID, Name of Item etc., related to both the following tables:
Repair Table-FK ItemID, consisting of Repair Tasks, Date etc.
Maintenance Table-FK ItemID, consisting of Maintenance Type, Date etc.
Thanks for any help

 

[John Vinson]

Could some one please advise if there are any implications in having a one to
many relationship in the following situation, ie a Primary key having more
than 1 relationship to separate tables
Item Table-PK ItemID, Name of Item etc., related to both the following tables:
Repair Table-FK ItemID, consisting of Repair Tasks, Date etc.
Maintenance Table-FK ItemID, consisting of Maintenance Type, Date etc.
Thanks for any help

I can't think of any issues; this is perfectly standard design. One
Item can have multiple instances of Repair; the same item can have
multiple instances of Maintenance.

The only caution would be that you cannot (easily) create a Query
joining Items to Repair and also to Maintenance in the same query. The
reason is that there is no relationship between Repair and
Maintenance; if a given item had three Repair instances and four
Maintenance instances, this query would show all twelve possible
combinations of the data from the two tables. The usual way to deal
with this is Don't Build That Query; instead use a Form based on
Items, with two Subforms, one based on each related table.


John W. Vinson[MVP]

 

[Roger Carlson]

This is fine if there is no relationship between Repair and Maintenance,
that is if Repair Items are independent of Maintenence Items.

However, if a Repair Item itself can be composed of one or more Maintenance
Types, then your design is wrong. In that case, your RepairID primary key
should go in your Maintenance Table as a foreign key.

In words, your design in words goes like this:

Each Item can have one or more Repairs
Each Item can have one or more Maintenence Types.

You be able to say that each Item had a certain number of repairs and a
certain number of Maintenance Types, but you won't be able to say which
repair is of which Maintenance Type.

However, if you put the RepairID in Maintenance Table as a foreign key, it
would go like this:

Each Item can have one or more Repairs
Each Repair can have one or more Maintenence Types.

With this design, you would be able to say which Maintenance Types each
Repair had.

It all depends on your real-world business rules.

--
--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