I’m having trouble with these entities:
Schedule - [id, time, days]
Class - [id, room_id, schedule_id, other_columns]
Room - [id, name]
As you can see, Rooms table only has a column id and a name, and Schedule only has days and time. I want to ask if is it necessary to make an entity even it only has one or two columns?
If it’s possible, I want either Room or Schedule columns to move to Class, since the schedule only have date and time which are just limited data.
yes
take the room table for example
here, you store the valid rooms
by using a foreign key in the schedule table, you ensure that only valid rooms are scheduled
you wouldn’t want an invalid room scheduled, right?
by the way, the FK relationship still would exist, and the value of having only valid rooms scheduled would be preserved, if, instead of a numeric id, the PK of the room table were the room name… then the FK in schedule would also be the room name
many database administrators will spit out their coffee when they read this, but data analysts all be like “so? that’s cool” – and furthermore, with the room name in schedule you don’t have to do the unnecessary join to translate the id
but the room table still needs to exist, even with just the one column
Okay, keep the table and make the Room name a primary key. Got it!
How about the Schedule columns? Should I just dissolve the table and move them to another table or should it remain and it will contain the FK room name?
i can’t really comment yet because i don’t understand what you’ve done
could you explain what the schedule table looks like? what does the days column hold?
if this is for a school, shouldn’t there be a column to describe the subjects being taught?
An entity is an entity; if you could make it part of another table, it’s not an entity, but a property of another entity.
It’s just days like [Mon], [Tues,Wed], [Thur,Fri].
Yes.
Here are the tables.
I’m not sure if I have to set up an entity for schedules since the data are limited.
Well at this point, days would be considered an unnormalized column; consider: How can you show me a student’s schedule for Friday?
section_subject feels like an unnecessary table; if you’ve drawn a 1-1 relationship between two tables, alarm bells should start going off.
Why do you join the “room” table directly with “class”, instead of with “schedule”?
At many schools, unis, etc. it is not uncommon that a “class” use different rooms through the week, if this is a functionality that needs to be added down the road. The current data model makes that difficult.
In short, why dont a “class” instead have multiple “schedule” records, and each “schedule” record would store the room the class is taught in. (I.e. one record for each different teaching session/class)
boy i’m glad i ducked out of this thread early, because i knew it might blow up
i mean, robust design is one thing, but let’s be careful of scope creep
Actually the school I’m working with, classes always has a fixed room, they don’t move at any time of the year.
Be very very wary of the word always. Lots of time when you dig in you’ll find exceptions anyway. Better to find them up front then at the end. Changing a diagram is cheap, refactoring an entire database + code is not.
Always? Yes
Always? Yes
Always? Yes
Really, always? Well … there’s this one a day a year where…
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.
