Table structure for planning an application in SQL DB

Question

I am working on a database for storing schedule information on request. I currently have a structure that looks something like this:

Table - Person: (key)ID, LName, FName, Phone, Email Table - PersonTeam: (from Person)ID, (from Team)ID Table - Team: (key)ID, TeamName Table - Calendar: (key dateTime)dt, year, month, day, etc... Table - Schedule: (from Calendar)dt, (id of Person)OnCall_NY, (id of Person)OnCall_MA, (id of Person)OnCall_CA 

My question is: with the Schedule table, you should leave it structured as is, where dt is a unique key or I need to change it so that dt is not unique, and the table looks like this:

 Table - Schedule: (from Calendar)dt, (from Team)ID, (from Person)ID 

and have multiple entries for each day, OR , it makes sense to just use:

 Table - Schedule: (from Calendar)dt, (from PersonTeam)KeyID - [make a key ID on each of the person/team pairings] 

A team will always have someone on call, but a person can be on call more than one team at a time (if they are in several teams).

If a completely different setting would work better, let me know!

Thanks for any help! I apologize if my question is unclear. I learn fast, but still am pretty new to using SQL daily, so I want to make sure that I use the best practices when I find out, so I don’t develop bad habits.

Answer 1

In my opinion, the answer may depend on whether the number of commands is fixed and rather small. Of course, whether command names are fixed or not may also matter, but this is probably more related to column names.

In particular, my opinion is this:

If the business requirement is to always have a small and fixed number of people (for example, three) on a call, then it might be more convenient to select three columns in the Schedule , one for each team to store the identifier of the assigned person, i.e. e. how is your current structure:

 dt OnCall_NY OnCall_MA OnCall_CA --- --------- --------- --------- 

with dt as the primary key.

If the number of commands (in the Team table) is also corrected, you can include the command names / pointers in the column names, as you are doing now, but if the number of commands is more than three, and this is just the number of commands in Schedule , which is limited to three, then you can simply use names like OnCallID1 , OnCallID2 , OnCallID3 .

But even if this requirement is corrected, it may turn out to be fixed today, and tomorrow your boss says: “We no longer work with a fixed number of teams (on call),” or “We need to increase the number of groups supported to four, and we it may be necessary to extend them further in the future. " So a more universal approach would be the one you plan to switch in your question, i.e.

 dt Team Person --- ---- ------ 

where the primary key will now be dt, Team .

Thus, you can easily expand / reduce the number of people on a call at the database level without having to change anything in the circuit.

---

UPDATE

I forgot to refer to your third option in my original answer (sorry). Here it goes.

Your first option (the one that is actually implemented at the moment), apparently, implies that each team can be represented (no more) by one person. If you assign surrogate identifiers for Person / Team pairs and use these keys in Schedule instead of separate identifiers for Person and Team , you probably will not be able to enforce the specified “one person per team on schedule” (or at least this it may seem somewhat difficult) at the database level, while using separate keys it would be enough to install Team as an integral part of the composite key (dt, Team) , and you will end up with no more than one team per day.

In addition, you may have difficulties with the person changing the team over time if their presence in the team was fixed in this way, that is, with the Schedule link to the Person / Team pair. You may have to change the Team link in the PersonTeam table, which will distort historical information: when viewing people who will be chimes on a certain day, the character shown by the Group will be who they belong to now, and not the one they made then.

Using separate identifiers for people and teams in Schedule , on the other hand, will allow you to freely change commands if you do not (Schedule.Team, Schedule.Person) link to (PersonTeam.Team, PersonTeam.Person) , of course.

Answer 2

• The current version, one column for each team, is probably not a good idea. Since you present the commands as a table (and not as an enumeration or equivalent), this means that you plan to add / remove commands over time. This will force you to add / remove columns to the table, which is always much more complicated than adding / removing multiple rows.

• The second option is the usual solution to such a problem. Safe choice. You can always define an additional foreign key constraint from Schedule (teamID, personID) to PersonTeam to ensure that you do not erroneously schedule the work of a person who does not belong to the team.

• The third option is pretty much equivalent to the second one, only you replace the complex natural key for PersonTeam for the surrogate simple key. Since the two components of the specified composite key are already surrogate, there is no advantage (in terms of immutability, etc.) before adding this extra. In addition, this would turn a very simple NM (PersonTeam) connection, which most DB / ORM managers would perfectly handle into a more complex object, which itself needs to be managed.

In the case of the Occam razor, I would do away with the extra surrogate key and use the second option.