Relationships design

[Tom]

Hi:

I have a question on how best to accomplish the following design
requirements:

I have the following tables:

tblPeople (identifies a person)

PersonID as PK
all the other usual fields
tblItem (identifies an item owned by a person. One person can own more
than one item, but an item can only be owned by one person. Items are
substantially the same and differentiated only by a serial number)ItemID as pk
ItemManufacturer
etc
tblRepair (identifies a repair action on a given item. Can be multiple
repair calls per item)RepairID as pk
etc

So here is the problem. When a person calls in to request a repair,
the maintenance person accepting the call may not know which of several
item owned by the person needs repair. It is only after a tech has
been dispatched to the site that we will know which item is being
repaired. So, how do I design the database so the person accepting the
telephone call can enter the repair work order information associated
with an item, when he doesn't know which item it belongs to (but
obviously does know which person the item belongs to, thus narrowing
down the universe of possiblities to a few).

My thought is to make tblrepair a child of tblPerson and include a
field for identifying the item number to be completed after hearing
back from the tech. That at least gets a tblRepair record associated
with a person so tracking down repair records will be a little easier.
The other option is to not have any relationships in tblRepair so a
"blind" record can be entered when the call is received, and later
associated with the correct Item.

Thoughts or recommendations?

Thanks

[Allen Browne]

If the specific ItemID is unknown, you do have a case for a PersonID foreign
key in tblRepair, even though this sometimes invovles redundant data. You
might also add an ItemTypeID to tblItem and to tblRepair, so (for example)
you can record that the repair was for Jo Smith's DVD player, even though
you don't yet know which DVD player needs repair.

Another option would be to add a tblCall (or tblBooking), when a person
calls to book in a repair. This distinguishes the request for repair from
the actual repair.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom" <rt*****@swbell.net> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I have a question on how best to accomplish the following design
requirements:

I have the following tables:

tblPeople (identifies a person)

PersonID as PK
all the other usual fields

tblItem (identifies an item owned by a person. One person can own more
than one item, but an item can only be owned by one person. Items are
substantially the same and differentiated only by a serial number)

ItemID as pk
ItemManufacturer
etc

tblRepair (identifies a repair action on a given item. Can be multiple
repair calls per item)

RepairID as pk
etc

So here is the problem. When a person calls in to request a repair,
the maintenance person accepting the call may not know which of several
item owned by the person needs repair. It is only after a tech has
been dispatched to the site that we will know which item is being
repaired. So, how do I design the database so the person accepting the
telephone call can enter the repair work order information associated
with an item, when he doesn't know which item it belongs to (but
obviously does know which person the item belongs to, thus narrowing
down the universe of possiblities to a few).

My thought is to make tblrepair a child of tblPerson and include a
field for identifying the item number to be completed after hearing
back from the tech. That at least gets a tblRepair record associated
with a person so tracking down repair records will be a little easier.
The other option is to not have any relationships in tblRepair so a
"blind" record can be entered when the call is received, and later
associated with the correct Item.

Thoughts or recommendations?

Thanks

[BillCo]

My immediate reaction is to say that you shoud avoid creating orphaned
records. As a rule it's not good practice and it messes up referential
integrity.

I presume there is a "tblOwnership" to create the many to many
relationship between items and people? Ideally you would like to use
the primary key from that table in tblRepair - but as I understand it
that's not an option...

If I were dealing with this, I would create 2 tables for repairs. The
first "tblRepairIncoming" which hooks onto tblPerson i.e. One person
may have many repairs incoming. This could take the basic repair job
data.
You could then have a function which copies the data into
"tblRepairRecieved", which uses the "tblOwnership" PK and either
deletes the record from tblRepairIncoming or somehow marks the record
recieved.

(all IMHO, of course)

[Tom]

Bell & Allen:

Thanks for your quick responses. I don't like the idea of orphaned
records either, which is what has me in a bind over this whole thing.

I think you are both suggesting the same thing - distinguish between an
incoming call requesting maintenance and a completed maintenance action
by making a table for each. Then including some kind of logic to
transfer a record from the calls table to the completed table as the
work is done in the field. I like that idea and will proceed down that
path - unless somebody comes up with a better suggestion ;-)

Thanks again!