There are two types of entities: user and room. A user can
- book room
- select room
- unbook room
- 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:
- roomStateId (there is an extra table LookUp_RoomState; possible states are: "free", "booked", "selected")
I didn't like that I have to create three columns that represent the state, especially the fact the
bookedBy are related with
roomStateId. I thought and created two tables as shown in the screen capture below:
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:
- roomStateId ("free"/"selected"/"booked")
The one thing I don't like in the last approach that meaning of the
userId is defined by another column -
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.