[SQL] Table Design/Normalization Question

[cismajor97]

I have a form that I am building and I've run into this problem. There is a section on the form that checks if a person wants to sign up for a AM shift or PM shift for a Show. What I need to know is how do I normalize the form to reflect that in the database? Here is what the user would see on the web form.

Show AM Shift PM Shift
--------------------------------------------------
Show #1 checkbox checkbox
Show #2 checkbox checkbox
Show #3 checkbox checkbox


Here is what I have so far in the table design:

Table Shows
--------------------
ShowID int PK
Name nvarchar(50) Not Null


Table Person
--------------------
PersonID int PK
FirstName nvarchar(50) Not Null
LastName nvarchar(50) Not Null


Table Shift
----------------
ShiftID int PK
Name bit Not Null

 

[mikeblas]

"Normalize the form to reflect that in the database"? What's that even mean?

AM/PM doesn't appear to be in your database schema. Is it possible for someone to sign up for both the AM and PM shifts, or should the database exclude that possibility intrinsically?

 

[generelz]

better yet, when the management decides to change the shifts so that there are three shifts now (morning, afternoon, evening), how could your database handle that? Why not just go ahead and build your shift table with a startTime and endTime column? That seems the most extensible option to me. Your actual application code should handle the translation between what a "AM/PM" shift means and the appropriate start/end time saved in that database table.

 

[cismajor97]

"Normalize the form to reflect that in the database"? What's that even mean?

I meant to write "Normalize the database to reflect the form."

AM/PM doesn't appear to be in your database schema. Is it possible for someone to sign up for both the AM and PM shifts, or should the database exclude that possibility intrinsically?

I set up the table so that the shift table so that if they wanted to change from AM to PM to something else they can edit that data. It is possible for 1 person to set up for both the AM and PM shift.

Why not just go ahead and build your shift table with a startTime and endTime column?

I'm open to do that, but I'm not sure how I would reflect what's in the database to what that in the web form. I'm using ASP.NET 2.0 with C#.

 

[Tytalus]

Well, as a suggestion if you are going to draw out textual tables I'd recommend putting code tags around it so the spacing is preserved. It could help you explain your current issue a bit better.

 

[LuminaryJanitor]

If I understand what's going on, this is what comes to mind (PKs underlined):

SHOW (ShowID, Name)
PERSON (PersonID, FirstName, LastName)
SHIFT (ShiftID, ShowID, Timeslot)
SIGNUP (PersonID, ShiftID)

 

[cismajor97]

Here is what the Shift Form looks like for more clarification.

This is the part I'm having the problem with. Associating the Person with the shifts that they selected. The AM and PM shifts won't change but the show dates will every year.

 

[TopGun]

Couldn't you just use 1, 2, 3?
Make 1 equal to am, make 2 equal to afternoon, and make 3 equal to pm.

 

[LuminaryJanitor]

This is the part I'm having the problem with. Associating the Person with the shifts that they selected.

That's what my SIGNUP table was for. For each user, for each checked box, there's an entry in the table. If you SELECT all entries for a given user, you can find out what shifts they're signed up for. If you SELECT all entries for a given shift, you can find out who's assigned to it.