Database Table Design

[clamum]

I need to create the tables for a web application and have a question about the best way to design the schema for the application's needs. I am using ASP.NET 3.5 and SQL Server 2005.

The relationship between the objects is fairly simple:

- There's a Location that has a time period, which I interpret as a beginning date and an ending date (spanning days, or perhaps a month or two and days).

- At each Location, there are Personnel. These Personnel can be active for each day in the time period, basically a binary yes/no toggle for each day (i.e. did they work that day).

I have a tblLocation that holds the Location's various info, and a tblPersonnel that holds each person's information. tblLocation has StartDate (datetime) and EndDate (datetime) columns.

So what I need to do next is model the relationship between a Location's time period and the Personnel for each day in that time period. So I was thinking of creating tblLocationPersonnel that had the primary keys of tblLocation and tblPersonnel, as well as a column Active (varchar) that contained the days the Personnel was "active" on that Location. I was just going to store the day numbers separated by commas but I realize now that I'll realistically need the year and month as well.

Another way would be to have the two primary keys and for the Active column, list a single date. So if a Personnel was active for 10 days on a Location, there would be ten records in that table for that relationship. There's going to be probably hundreds or thousands of Personnel, and possible the same number of Locations, so the record count in that table could get quite huge; does this way become inefficient then?

I should note complete normalization is not a requirement here. Any ideas on modelling this relationship? Thanks for any input you can share.

 

[EagleKeeper]

You were on the proper track until you looked at a record for each day on an active person.

The only reason for that would be if you needed to track daily related info.

Other than that the join table containing the location ID, personal ID, active indicator and current days active.

How do you intend on tracking when a person becomes active or inactive? Or do you need to keep track of such data?

 

[clamum]

Yes, the tracking of the active/inactive needs to happen. If a person becomes inactive for one of the days, the application would just rebuild the ActiveDays string and it would not contain the day that was toggled off (or remove that day's record if going the second route I mentioned above).

The way the GUI is planned to be designed is in a grid format, like an Excel spreadsheet, with the days as columns at the top and the personnel as rows to the side. Clicking in a cell makes that person "Active" for that day.

Actually I will need to track who last updated the record and at what time, but that can just be done by a couple extra columns in this table.

So you think a column with the dates concatenated (dd/mm/yyyy, separated by commas) would be a better way to go than a single date but multiple records? I was actually starting to lean toward the other way, but as you mentioned there is no need to track anything besides a date (existence of a date meaning person was "active" on that date) and the fact that entries need to be traceable can be done by a ModifiedOn and a ModifiedBy column.

 

[GilletteCat]

Somewhat of a right track . Sorry, I am not being an a$$. All's well with the three tables. the tblLocation describes a location, which is not transactional and does not change, whether the personnel is there or not, such as address of it and so on. That was the reason you correctly created the transactional table tblLocationPersonnel. Move the dates from tblLocation onto tblLocationPersonnel, get rid of the Active, which is obsolete, since each row in that table represents an activity of a person at a certain location. If he/she was not active, there would not be a record created. So, to sum it up: tblPersonnel lists available people, tblLocation lists available locations and tblLocationPersonnel ties them together on each occurrence of a person being active (on duty) at some location. It does not contain any other records.

 

[clamum]

Originally posted by: GilletteCat
Somewhat of a right track . Sorry, I am not being an a$$. All's well with the three tables. the tblLocation describes a location, which is not transactional and does not change, whether the personnel is there or not, such as address of it and so on. That was the reason you correctly created the transactional table tblLocationPersonnel. Move the dates from tblLocation onto tblLocationPersonnel, get rid of the Active, which is obsolete, since each row in that table represents an activity of a person at a certain location. If he/she was not active, there would not be a record created. So, to sum it up: tblPersonnel lists available people, tblLocation lists available locations and tblLocationPersonnel ties them together on each occurrence of a person being active (on duty) at some location. It does not contain any other records.

Haha, no, no offense taken. The StartDate and EndDate columns need to stay in tblLocation though because they are a property of that location, just like its ID or Name.

The Active column in tblLocationPersonnel contains the date the person was active, which also needs to be stored. This date falls between or on the StartDate/EndDates of the location. So a person can (will) be active on more than one day for a location.

Something else I just came across: Reporting will need to be done on a person for activity during a given time period. So if there was a record stored for each date the SQL would be simple, otherwise if they were concatenated I think there would have to be some parsing done of the string to check if the dates were in the range.

 

[GilletteCat]

The StartDate and EndDate columns need to stay in tblLocation though because they are a property of that location, just like its ID or Name.

Why, what do they represent?

The Active column in tblLocationPersonnel contains the date the person was active, which also needs to be stored. This date falls between or on the StartDate/EndDates of the location. So a person can (will) be active on more than one day for a location.

This was exactly the reason to move those dates, Start and End, from the Location to the join table. Instead of the Active containing the date, those two would contain the date/time span of the beginning and end of each occurrence (i.e. shift) for any given person at any given location. Of course a person can be associated with any location id more than once. Hence a new row for each such occurrence. Didn't you just mention reporting? That is exactly what i had in mind.

 

[clamum]

Why, what do they represent?

Those dates represent the period of time personnel are allowed log activity for a location.

This was exactly the reason to move those dates, Start and End, from the Location to the join table. Instead of the Active containing the date, those two would contain the date/time span of the beginning and end of each occurrence (i.e. shift) for any given person at any given location. Of course a person can be associated with any location id more than once. Hence a new row for each such occurrence. Didn't you just mention reporting? That is exactly what i had in mind.

Ah, I see what you're saying regarding moving the dates. I still think the Start/End Date need to be tied the location, because they are a property of that location, but I see what you mean by storing a time span.

Rather than an Active column with a single date, make it like ActiveStart and ActiveEnd and store the span. The activity spans can be broken for personnel at a location but yes, that would just be another span record for that location. Hmmm.

I see now too that there's probably going to have to be another table (possibly two) that stores data like this, but not for personnel. For each location there are several attributes that are a yes/no for each day, just like employees.

 

[TechBoyJK]

Everytime I use prefixes on my tablenames like "tbl" the db syntax nazis come out and ask why I do that.

 

[GilletteCat]

Originally posted by: TechBoyJK
Everytime I use prefixes on my tablenames like "tbl" the db syntax nazis come out and ask why I do that.

Pleeeease, don't do there! This will only hijack this thread. It always does :|

 

[GilletteCat]

Just think of it as your basic Customer, Product, Order ecommerce situation. Of course the same customer can come back numerous times and make new purchases, I think you would even like that . And obviously he can buy the same product again and again. Hence, in the Order table you record the customer id, the product id and the timestamp of the transaction. In your case, Customer is Personnel, Product is Location and they can be joined together many times in the LocationPersonnel (Order) table. The difference is, as I said earlier, instead of just the timestamp of the occurrence, you might want to record the duration. Hence, start and end. You should get good performance for the future reporting because you'd be dealing with foreign keys (primary on their respective tables), so, definitely indexed and integer. Make sure to make the compound key on the join table by adding the dates as well, so you will be set with necessary indexes for fast searches later.
I see why you want to have dates on the Location table. Well, you could simplify an availability of a location feature by having your beloved IsActive switch there. You could then simply render any location inactive for registering at through a GUI of your future App by flipping a switch in it. Just a thought. If you want the dates to provide this functionality, it's your design, after all