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:
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:
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).
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?
Once I have this down then I just have to map my tables and my classes
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).