There are two types of entities: user and room. A user can

  1. book room
  2. select room
  3. unbook room
  4. unselect room

"Selected room" is a state that indicates when a user has decided to book a room and so he/she selects the room of relevance, but still has not pushed a button 'Book'.

Firstly I have tried to create one table for room with the following columns:

  1. roomId
  2. roomNumber
  3. roomStateId (there is an extra table LookUp_RoomState; possible states are: "free", "booked", "selected")
  4. selectedBy
  5. bookedBy

I didn't like that I have to create three columns that represent the state, especially the fact the selectedBy and bookedBy are related with roomStateId. I thought and created two tables as shown in the screen capture below:

Sample 1

I didn't like that now I have too many tables and when I want to check the state of the room I have to rely on the state of the system. Moreover, it can potentially produce collisions because both tables can be updated independently. In the template that follows, I returned to the one-table arrangement:

  1. roomId
  2. roomNumber
  3. roomStateId ("free"/"selected"/"booked")
  4. userId

The one thing I don't like in the last approach that meaning of the userId is defined by another column - roomStateId, when roomStateId is booked than userId has value bookedBy, when roomStateId is selected then userId has value selectedBy. I have also thought about some another variants - move roomStateId and userId to a separate table, rename userId to stateChangedByUserId.

I now think that it is impossible to create an ideal schema and all depends on the use cases; however, maybe someone have some ideas on it? I really don't like when one column can have many values dependent on some conditions as userId on room table, but currently I have stopped on the third variant, because it seems handy to me at this moment.

  • 2
    Lookup tables are usually a superior choice compared to CHECK constraints or ENUMs which are the other two usual methods to ensure referential integrity. – Vérace Sep 8 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.