DB design

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.

Ideas ?

1800 rows is a very, very small number. I’d go with approach #2. It even gives you the flexibility to customize the checklist for one or more rooms.

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.

@Dave S Did you mean ‘The second method’, right ?

yes, yes I did…
“the first method” I refered to in my post was the “long” method… not to be confused with #1 and #2 from yours… sorry :slight_smile:

#2 is cleaner method.

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

definetly option #2 is the way to go

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.

Thanks everybody, your feedback was very helpful.

Add another Column for a Date(Time) Value in the “Link”-Table and you can maintain even a history of room cleanings. :wink:

Its in there already :slight_smile: roomID, cleaningID, dateTime, cleanersName

“Querlesen” (cross check?) FTW ! :smiley:

Hehe, skim reading or skimming over or to skim over, I often suffer from this :smiley:

But it doesnt work as a checklist.

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.

Erm, I don’t see the issue, sorry.

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 :smiley:

I finally went with the ‘create a third table’ idea rather than adding a column for each item (activity) in the existing rooms table.

It’s amazing (for me as a beginner in coding) how much easier and cleaner code gets when it’s well thought.

The discarded alternative involved many many more lines.

Thank you all.

this is the step that most developers try to skimp on

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… :wink:

@Dave S

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)