Understanding keys and constraints...

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.