PDA

View Full Version : Design Help

cmptrguru7
06-14-02, 19:49
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.
r937
06-18-02, 15:21
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/
Manish
06-23-02, 11:32
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.
cmptrguru7
06-24-02, 18:06
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!!
r937
06-24-02, 19:41
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