Inventory/Loan Database

[rienada]

Morning Gents/Ladies, I'm having an issue with the relation between tables. Any suggestion would be appreciated. Basically I have an Inventory which holds equipment and that I sign out to individuals. I would like to be able to keep track of all my inventory from sitting in my shop or some Bloggins desk.

My Design

t_Person -->PersonID PriKey

t_Laptop -->LaptopID PriKey
-->PersonID Foreign

t_Phone -->PhoneID PriKey
-->PersonID Foreign

t_Printer -->PrinterID PriKey
-->PersonID Foreign

A Person can have as many items he wants, an Item can only belong to 1 person.

When PersonID=0, it means the item is sitting on my shelf. The issue I am having is how to query all the information. IE, I want to see everything PersonID=3 has on loan.
PersonId=3 has 2 Laptop, 1 Phones, 0 Printers

Outcome:
Bloggins Panasonic Toughbook 1, Panasonic Toughbook 2, Blackberry, Nil

Hopefully I've given enough detail for some input. Thanks in advance.

[xpartmgr]

I would personally put all the equipment into a single table with an equipment type id. However, you should be able to use a union if you want to use separate tables. Then all you have to do is query where PersonID = whatever personID you want.

[drjohn]

You need multiple tables to do this properly.

person--<loans>--equipment

person stores people details, with a PK = PersonID
equipment stores ALL the stuff you loan out, with a PK of eqipID, and includes a field called eType for type of item (plus other fields you may need)
for example equipment {equipId, eType, description, manufacturer, cost, rentalFee, modelNum, whatEverElse}

loans records all loans you make
loans (PersonID, equipID, dateOut, dueBack, dateReturned) and perhaps a few other things you personally might need. It has a joint Pk of PersonID and equipID

this way you can check all loans by any person, all loans of any bit of equipment, all loans on any type of equip, find all stuff due back or a particular type of equip due back, and all stuff that is over due or a particular type of equipment that is overdue. ETC, etc, etc

Basically your original design is very poor indeed. Very inflexible. AND most important of all, stores no historical info, as you are resetting a loan to personID=0 when it is returned and destroying your history.

PS don't waste typing time and risk typos by preceding each table with that t_ ! eg t_Person. that style of notation is rarely used and is from a different era of database design. Just use the table name, it is all that you need.