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

    New table to show relationship?

    Hi everyone!

    I have the following question since I am confused about the notation of a E/R-diagram I'm trying to create in Visio.

    Background:

    I have a projekt-table (entity) and an employee-table (entity)

    I think I have to create a new table f.i schedule to combine the employees with the different projects....is this a correct approach?

    The thing is that I let one relationship-arrow point from the Shedule-table (entity) to the employee-table (entity) and another relationship-arrow point from the Shedule-table to the Project-table....

    In this way the Shedule-table (entity) gets a forreign key from the other tables... but since one of the arrows point from the shedule-table to the employee-table it seems inconvenient to say that one employee has many Shedules.....(I think you see waht I mean)

    Please help

    Thanks

    /Marcus

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm going to reply using SQL, because it works better for text messaging and is pretty widely understood. My suggestion is:
    PHP Code:
    CREATE TABLE employees (
       
    employeeId INT NOT NULL
       PRIMARY KEY 
    (employeeId)
    -- 
    add other columns as needed
       
    )

    CREATE TABLE projekts (
       
    projektId INT NOT NULL
       PRIMARY KEY 
    (projektId)
    -- 
    add other columns as needed
      
    )

    CREATE TABLE schedules (
       
    scheduleId INT NOT NULL
       PRIMARY KEY 
    (scheduleId)
    ,  
    employeeId INT NOT NULL
       FOREIGN KEY 
    (employeeId)
          
    REFERENCES employees (employeeId)
    ,  
    projektId INT NOT NULL
       FOREIGN KEY 
    (projektId)
          
    REFERENCES projekts (projektId)
    ,  
    dateBegin DATETIME NOT NULL
    ,  dateEnd DATETIME NULL
       

    This makes each schedule row dependant on an employee and a project. It also explicitly establishes that schedules must start, and may or may not end. It implicitly allows an employee to work on more than one project at a time, and for them to work on a project for a while, get pulled off for a while, then return to the project. These are key items that many scheduling schemas overlook!

    -PatP

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

    Smile

    Thanks alot for your answer!

Posting Permissions

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