Results 1 to 5 of 5

Thread: Design Help

  1. #1
    Join Date
    Feb 2002
    Posts
    43

    Design Help

    I have a situation where I am creating a table that needs a triple field primary key. However, one of the fields may be null. I do not believe you can have a null field part of the primary key? I am using mysql as the DB enviornment. I am creating a hotel like structure where a room can be in a particular tower (3 choices), and each room can change type depending on setup. The rooms are also designated by number based on tower and inside each room there can be up to 4 different sides which need to be associated with a particular person occupying the room. If anyone can help me with this or if you need further information please let me know.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    nulls in pks

    the primary key cannot by definition have a null in any of its columns

    a pk doesn't really do anything for you unless you have foreign keys in other tables that refer back to the pk

    perhaps you should consider using an autonumber as a surrogate pk

    makes the fks neater, too

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Dec 2001
    Location
    Delhi/Bangalore, India
    Posts
    74
    There's no reason you can manipulate the database a bit so that this null-pk problem gets out of way.

    If you can post the exact table strcuture,etc., we may be able to help you design it in a way so you can incorporate the three keys without trouble.

  4. #4
    Join Date
    Feb 2002
    Posts
    43
    Thanks for everyones help. The structure for the two tables involved looks something like:

    Room Table:
    Building (3 choices, PK)

    Room (PK)

    Side (A, AA, B, BB, also PK, but some rooms do not have sides so this can be null.

    Type (Many types can change affecting number of sides.)

    Occupant Table:
    Name
    Address
    etc...

    Building (FK)
    Room (FK)
    Side (FK)

    I think I may have figured it out, but any more help would be appreciative. The idea is there is a room say Building K room 100 side A and there is a room say Building K room 100 side B. I need to be able to associate the correct room number and correct side with a person. Each year the type of room can change to create either 2 or 4 spaces. Some rooms are completely private and have no sides (hence the null problem.

    Thanks again!!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my suggestion was to use a surrogate key

    so RoomTable would have
    roomid PK
    building
    room
    side

    and OccupantTable
    name
    address
    roomid FK

    the disadvantage of this is that roomid is not a natural key (see http://rudy.ca/20020620.cfm), and you cannot run a uery on OccupantTable to find all occupants in building K without doing a join

    another solution is to go with what you have but use "none" or "XX" as a real value for side instead of null, that way all the PK values will be not null

    rudy

Posting Permissions

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