Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2011
    Posts
    8

    Designing for varying fields

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    8
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by johnharris85 View Post
    Would it be possible for you to outline the structure of the new tables I would have please?
    sure
    Code:
    CREATE TABLE loanable_items
    ( i_id INTEGER NOT NULL PRIMARY KEY 
    );
    
    CREATE TABLE keys
    ( k_id INTEGER NOT NULL PRIMARY KEY 
    , CONSTRAINT key_item
       FOREIGN KEY ( k_id ) REFERENCES loanable_items ( i_id )
    , k_code_one VARCHAR(99)
    , k_code_two VARCHAR(99)
    , k_code_three VARCHAR(99)
    , k_code_four VARCHAR(99)
    );
    
    CREATE TABLE cards
    ( c_id INTEGER NOT NULL PRIMARY KEY 
    , CONSTRAINT card_item
       FOREIGN KEY ( c_id ) REFERENCES loanable_items ( i_id )
    , c_expiry_date DATE
    , c_deleted CHAR(1)
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2011
    Posts
    8
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by johnharris85 View Post
    and my loans table has a FK that is i_id? Then I join them all up to do queries?
    yes


    Quote Originally Posted by johnharris85 View Post
    Let's say not every key has 4 codes
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2011
    Posts
    8
    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!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    with that expanation, your original design is fine --

    keys:
    | k_id | k_code_one | k_code_two | k_code_three | k_code_four |
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2011
    Posts
    8
    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?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2011
    Posts
    8
    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!

  12. #12
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by johnharris85 View Post
    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).
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •