Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Sweden
    Posts
    17

    Question New table or new primary key? PLEASE HELP

    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
    Attached Thumbnails Attached Thumbnails bilden.png  

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: New table or new primary key? PLEASE HELP

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

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    3

    Creating another table

    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.

  5. #5
    Join Date
    Apr 2004
    Location
    Sweden
    Posts
    17

    Thumbs up Thanks!

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

    /Marcus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •