Understanding keys and constraints...

[JohnnyD]

Hi,

I am trying to figure something out for an app I'm developing. I hope some people on here can point me in the right direction.


I need to have a database table (SQL Server 2005) which stores various shifts that people can work during a given day (normally either one or two shifts per day depending on how busy we expect to be) and the start/end times, etc.. I'll call this table Shifts.

I then need to have another table linked to the shifts table which stores batches of orders. This table is going to basically be a linking table which links Orders to Shifts but stores more information about which employee is assigned to that batch, and things like that. I will call this table Batches.

So I need to set up say two shifts for a given day, lets say today - 30/03/2010. The shifts will be numbered 1 and 2 respectively. Shift 1 starts at 8am and finishes at 4pm. Shift 2 starts at 4pm and ends at 12am. The start time of shift n+1 can never be earlier than shift n.

Then I need to assign say, 3 employees to that shift. So in my Batches table, I need to link it to the appropriate shift and also link it to an employee. Obviously one employee can't be assigned to the same shift twice so I'm leaning towards a composite primary key based on the link to shift and link to employee. The thing is, I also need each individual batch to have a batch number associated with it, so I can record that in each order so I know which orders are linked to each batch. I am confused about whether to have a BatchNumber column which would be the primary key instead. If I do this, how do I enforce the rule that one employee can't be assigned to the same shift twice? Using constraints?

Let me document the table structures I'm thinking of creating...

So I have one table with employees which has the following columns:

EmpID - PK - autonumber int.
Firstname
Lastname
etc...


Table - Shifts (I'm thinking PK will be composite of Date and ShiftNo, but again not quite sure if I should just have a ShiftID column or something because I need to link the Batches table to it?):
Date
ShiftNo
StartTime
EndTime

Table - Batches (I think here the PK would be a composite of all three fields because you could only ever have one batch number assigned to one packer on one shift):
FK_ShiftID - ??
FK_EmpID
BatchNo

Table - Orders:
PK_OrderID
FK_BatchNo
DeliveryDate
PackedDate
etc..

I think my main issue I'm struggling with here is trying to understand is, if you add a key like ShiftID to the shifts table, would that have to be the Primary key? Or could I keep the Composite PK of Date and Shift No and have a Shift ID that wasn't a PK? Or, to ask the same question in a different way, does a surrogate key always have to be a primary key?

Sorry if this is a ludicrously long-winded post, as you can probably tell - I'm not doing very well in understanding database keys! I also think I may be overdoing the composite primary key idea, but would very much appreciate feedback.

Please don't think I'm trying to get people on a forum to do my work for me, I just really need to learn this stuff fast!

Many thanks,

John.

[JohnnyD]

I have just been playing around with indexes and it seems I can make a surrogate primary key on a table eg - ShiftID and also set up a unique index consisting of the Date and ShiftNo columns which makes sure that you can't have for example two shift 1's on the same day.

Is this the right way of going about things when using surrogate keys?