I'm currently learning some basic database design as part of a larger personal project (written in python, fwiw). I want to record loans to students. The loan can either be of a key or a card. I have identified the following tables:

students
s_id(PK), s_forename, s_surname, s_email

keys
k_id(PK), k_code_one, k_code_two, k_code_three, k_code_four

cards
c_id(PK), c_expiry_date, c_deleted

loans
loan_id(PK), returned, date, date_due, notes, s_id(FK), ?????

Now obviously I need fields for foreign keys from keys (k_id) and cards (c_id) however only one of them is going to be used for each loan. How could I implement this? I don't really want a bunch of NULL values.

Also, as a secondary question, if I were to add a new table:

rooms
room_id(PK), key_id(FK)

and wanted to relate it to students. Would I have student_id as a FK in the rooms table (as occupant or something) or would I have room_id as a FK in the students table (as current_room or something).

Thanks for your understanding and help!

the first question is a good opportunity to apply the supertype/subtype structure (search on that phrase for loads of background info)

basically, you will set up a supertype or master table, called something like loanable_items

this table will have information common to all loanable items -- although the examples you gave of keys and cards don't actually have any columns in common other than their new PK

the PK of the loanable_items table will then be used as the FK in the keys table (k_id), as well as the FK in the cards table (c_id), and both of these tables are then subtype tables

thus, the keys and cards table will never both have the same id value, as it will be either one or the other

the loans table will also reference the loanable_items table, thus solving your first question

as for the second question, the answer is simple -- in any one-to-many relationship, the PK of the "one" table is referenced by the FK of the "many" table, so if a student can only be in one room at a time, then the room is the "one" table

Thanks so much for your help. I have looked at some docs on supertype/subtype and now kind of understand the concept. Would it be possible for you to outline the structure of the new tables I would have please? I think that would really help my understanding.

With regard to the rooms table, a student (one) can be assigned to more than one (many) room, but a room can only have one student so I guess it is correct to put s_id as a FK in the rooms table?

rooms
room_id(PK), s_id(FK)

Once I have this down then I just have to map my tables and my classes

sure

Thanks! So let's say a key looks like this:

k_id: BH21G
k_code_one: 25001
k_code_two: TGG
k_code_three: 4
k_code_four: 78

and a card looks like:

c_id: 5678123
c_expiry_date: 25/09/2011
c_deleted: 0

in the tables you've outlined would look like this:

loanable_items:
| i_id |
| BH21G |
| 5678123|

keys:
| k_id | k_code_one | k_code_two | k_code_three | k_code_four |
| BH21G | 25001 | TGG | 4 | 78 |

cards:
| c_id | c_expiry_date | c_deleted |
| 5678123 | 25/09/2011 | 0 |

and my loans table has a FK that is i_id? Then I join them all up to do queries?

Let's say not every key has 4 codes, same principle and create another super/sub? Super as key_types then the subs as key_with_one_code, key_with_two_codes etc etc?

yes


not quite the same issue as supertype/subtype, but rather, just a simple one-to-many relationship

new table key_codes has k_id and code, with up to 4 rows for the same k_id

Thanks! You've been a real help! I've been thinking more about the key codes problem and it's actually a bit more complicated than I originally thought. For each key, there can be a minimum of 2 but max of 4 codes. Not all of the codes are unique. So the first code on a key might be BH which is actually a general location designation, so many keys will have BH. It's the composite of the codes which is unique to a key. Therefore a list of key codes will actually have an many-to-many relationship with keys in a lot of cases. Also I need to preserve the order in which the codes come (the location designation 'BH' needs to be the first code, etc etc). I'm not sure what a good solution would be in this case.

Each key can have 2-4 codes, each code can belong to a number of keys. The position of each code on the key is important (1, 2, 3 or 4).

Thanks again!

with that expanation, your original design is fine --

keys:
| k_id | k_code_one | k_code_two | k_code_three | k_code_four |

A lot of keys only have 2 codes (some have 3, some have 4). In a lot of cases codes 3 and 4 will be NULL values? Is that ok?

yes, that's fine

except if you are doing a reverse search, i.e. find all keys that have a certain code in any of the 4 positions -- it'll be messy and inefficient

No, I will always know which position each code should be in so that search wouldn't ever have to happen. Your advice has been really helpful, thanks!

It looks like your room table holds a foreign key to a key, so can't you already determine which room a student is in based on the key(s) they've been loaned?