Hi everyone!

I'm a newbie and right now I'm getting crazy about the primary keys.

From what I've heard one doesn't have to use a forreign key as the primary key in a one-to-many relationship if it will not function as a primary key due to the use of the table. For instance in the attached file where I have decided to not use the Employeenumber as a primary key in the WeekSchedule-table. This because the table shall, except from showing which employee who is going to work on which week, day and workshift, also show the emplyees wishes. In other words the table first get filled up with wishes. Later one can choose who is going to work by setting the Allowed variable to TRUE or FALSE.

Because of that a certain employee can make many wishes - the Emplyeenumber will not be unique. Instead I gave the table a counter, ID, to function as primary key.

But I don't know if this is the most appropiate approach. I think there will be alot of redundancy beacause of the Allowed variable. An employee can occur infinite many times with ID and Allowed as the only difference. Perhaps I should make two different tables - one for wishes and one for WeekSchedule? Or make a new primary key ut of ID, Employeenumber, Week, Day and Shift?

It seems like the WeekShedule table in its present form doesn't agree with the second normal form - Am I right?

PLEASE PLEASE PLEASE help me sort this out...

Thanks in advance!


/Marcus

Why is the key not (Employeenumber, Week, Day, Shift)? Surely you would not want the same combination of these entered twice?

I'd suggest nearly the same approach that andrewst did. I'd leave the primary key as it is, but I'd add (Employeenumber, week, day, shift) as a Cadidate or Alternate Key. That will prevent duplicated entries for those columns, express a bit more information for the optimizer to use about your table, but still provide the simplicity and efficiency of a single column primary key.

Just an observation, but I'd double check your column names to ensure that they aren't reserved words by either your database engine language or your likely choices for programming languages. The words "week" and "day" are reserved in many languages, and "shift" is reserved in some of them. While most languages allow you to work around problems like this, there is no point in designing something so that it causes you problems from the start!

-PatP

Hi

I think that the relationship between employee and schedule can be many to many. Also, the sometimes employee can get the same pattern of schedule which may create a data redundancy. Therefore i think u can do this:

Primary Key Attributes
----------------------------------------------
Employee ID --> empSurname, empLastname, empEmail, ......etc/
Schedule ID --> Week, Day, Workshift
Employee ID, Schedule ID --> Allowed

Now u have 2 entities "EMployee" and "schedule" and an associated table "Employee_shift" between them.

Thanks alot everyone for your comments!....they really helped alot...

/Marcus