Multiple tables

Anon

Hi there,
I have 3 tables, A -- a list of items, B -- a list of which are on loan (using forgein keys), C -- a list of items being repaired (also made be using forgein keys).
Now, how do I make sure that I don't have an item in the loan and reapir table at the same time?

Thanks,

Hereward

bfinucan

Hello,
Can you give the field names for each table? I'd like to see your organization logic. I can think of a few possibilities, but clarification will be a great help...

Anon

Here is the table structure, it is for a hospital loan system, part of an A-level computing coursework

A -- equip

| eid | desc | cost |

eid -- unique autoinc equipment id
desc -- text description of item
cost -- cost to replace it
this would just be a list of the types of items for hire, not how many are in stock, and so is limited to 10 I think, as their are only ten different items they hire

B -- stock

| sid | RELeid |

sid -- unique autoinc stock id
RELeid -- FK linked to equip.eid
this contains the list of actuall items, and the number of them, not the type, (equip.eid)

C -- paitent

| pid | surname |

pid -- unique autoinc paitent id
surname -- paitent's surname
(I will add more details later i.e. address, telephone...)
just a list of paitents

D -- loan

| lid | RELsid | RELpid |

lid -- unique autoinc loan id
RELsid -- FK linked to stock.sid
RELpid -- FK linked to paitent.pid
table linking paitent to item

E -- repair

| rid | RELsid |

rid -- unique autoinc reapir id
RELsid -- FK linked to stock.sid
table listing what items are being repaired

What I want to do is make sure that an item can't be on loan and being reapired at the same time.

Thanks,

Hereward