Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Database Design Question

    Hi Guys

    Iam posting this problem in this oracle forum also to get more ideas...


    Iam trying to understand the existing database datamodel.

    In this Project ,Task,Assignment,Resource are four entitiys.

    Primary Key Information:

    Project :Proj_Id
    Task :Proj_id,Task_uid
    Resource :Res_uid,Proj_id
    Assignment :Assn_uid,Proj_id

    In a given model thare is
    1.one to many relationship(identifying) between Project and Task.
    2.one to many relationship(non Identifying) between Task and Assignments.
    3. one to many relationship(non Identifying) between Resource and Assignments.

    Surprising to me thare is no relationship shown between project and Assignments in datamodel diagram.But Assignment entity has Proj_id as part of primary key.

    Is Proj_id in Assignments entity came due to relation with tasks and resource entity.

    Regards
    Umashankar

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Database Design Question

    Those primary keys look a bit strange, but could be correct. From your description I understand the ERD to be like:

    PHP Code:
    [Project]
        |
        ^
      [
    Task]  [Resource]    
        |        |
        ^        ^
       [
    Assignment
    Resource appears to be independent of Project according to your relationships, but not according to the primary key of Resource - or did you omit a relationship between them by mistake?

    Since the primary key of Task is (Proj_id, Task_id) then it follows that the Assignment table must also have those columns for its foreign key to Task.

    Assignment represents the assignment of a Resource to a Task on a Project. If a particular Resource can only be assigned to a particular Task once, then there should be a primary/unique key on Assignment(Proj_id,Task_id,Resource_id). The PK shown uses a combination of Proj_id and a surrogate key called Assn_uid, which may be correct but does not enforce uniqueness of Resource/Task assignments.

    So to answer your specific question, proj_id is in the Assignment table because it is part of the foreign key to Task.

Posting Permissions

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