Thread: Foreign Keys

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Australia
    Posts
    20
    Rep Power
    0

    Question Foreign Keys


    I am looking for some help regarding my database structure for an internal time recording system I am creating...

    I am in the process of converting my ER-Diagram to relations. One of my entities called 'Project' has a number of fields, three of which are:IT_PROJMGR, BUS_PM and BUS_OWNER. I have another entity called 'Employee' with its primary key called EMP_ID. The three fields in the project entity are all foreign keys referencing the EMP_ID field in the employee table. So essentially, there is a many to many relationship between the two, even though there is only one employee that can take on any of these roles.

    How can I go about transforming this to a set of relations? One method I thought of was to create a third entity - 'Projemps' and have PROJ_ID and EMP_ID as a composite key, then have a third field defining their position.

    But I am not happy with this structure as it doesn't easily convey meaning. Is it possible to have the three fields remain in my project entity (relation) and have three foreign keys referencing the same field from the employee table???

    Any help or suggestions you could offer me would be much appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Can you diagram what is in your head? I have found that a diagram or an hierarchy chart, in your case, helps to shed light on a solution. A diagram could even help us help you.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Location
    Australia
    Posts
    20
    Rep Power
    0
    I have attached an ER-diagram of the structure I have created to date.

    I have left the cardinality off the relationship between project and employee because this is partly what I am unsure about. I am also unsure of whether or not I can only have one relationship here, when I think that perhaps there should be three, since IT MANAGER, BUSINESS PM, and BUS OWNER all reference emp_num in the employee table.

    I have the same sort of problem between the user and employee entites. But here I have simply created 2 relationships to show that both supervisor and emp_id reference the emp_id attribute in the employee table.

    I'm not sure which method is correct, but I think its the second (ie. the one used between employee and user). However, if it is this one, can these entities then be transformed directly to relations???? I'm not sure whether or not you can have multiple fields in one table as foreign keys referencing the same field in another table.

    Help please???
    Attached Images

IMN logo majestic logo threadwatch logo seochat tools logo