Hey Guys, maybe you can enlighten me on what’s the best design for my (Cube) SQLite DB
I currently have a HotelDB, which has a ‘rooms’ table with all the attributes (name, type, status, etc) of every room in its columns.
Now, I must build a housekeeping checklist (all boolean values).
All the items in the checklist will be common to all rooms. I mean, every room has all the items in the checklist.
So… two possible approaches.
1_ Create (and eventually delete, when necesary) a new column in the already existent ‘rooms’ table for each item in the checklist. This implies creating and dropping columns when new items are added or deleted, which kindda scares me a bit.
2_ Create a new table (RoomNumber TEXT, CheckListItem TEXT, Value Boolean) with as many rows as (RoomAmount * Items in the checklist). This means that I’ll have to use more JOINS, to get the values from the checklist from a certain room, and if the Hotel has 60 rooms, and the checklist is 30 items long this table will have 1800 rows.
Database tables should alway be designed to be longer than wider…
by that I mean, used keyed values and have multiple rows per data entity, as opposed to a single record with fields for every attribute type. The first method allows you to add more attributes without rebuilding the entire table and changing all the logic involved.
You should probably think a bit bigger in your database design, eg for cleaning you should have a schedule who cleans which room when. If a cleaner is sick or on holiday which rooms need to be covered by somebody else. When was the room cleaned last? A used room needs to be cleaned, an unused one might need cleaning after 3 days. Etc etc
Create a table for rooms, a table for cleaning activities and a table that links the two which is just roomID, cleaningID, dateTime, cleanersName. You dont need a boolean, if its in the link table then it happened. Technically you could get away without the table for rooms but that is going a bit minimalist, keeping the rooms table allows you to add functionality down the line without re-engineering your DB and app.
If there are 3 types of thing to check which can happen independantly, then if there are NO linked rows, for sure, something hasnt been done.
But if there are 3 rows, you dont know that there should be a 4th and it hasnt happened.
Whereas if there are 30 rooms and 10 activities with a date or a boolean,
you can see at a glance how the 10 activities you need done are progessing.
And you can add new activity types later with ease.
But the time and date of the cleaning event is in there:
roomID, cleaningID, dateTime, cleanersName
Its a pretty easy query to produce a cleaning list with the date/time and cleaner name next to each entry for a room and date unless the cleaners go in there over midnight but I don’t think that happens, I think the customers would get a bit angry with the vacuum cleaner on at that time of night
But if you use for example some schema like: roomID, activityID, activityKind, activityDuration, …, dateTime, cleanersName (and so on…), you can protocoll anything very detailed and flexible.
BTW: activityKind f.e. would be another Table in which those “Kinds” and alike, are managed…
I dont’ say it for me, because I am far from pro in this industry, but I understand that thinking needs time, and time is money… So in the equation the budget is a factor to consider when evaluating the quality of the design… (I guess)
