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

    Unanswered: Database Design Question

    Hi Guys

    I have one parent entity that is related to two other entity with one to many relatetion ship (i,e main entitys primary is becoming part of primary key of other entitys i,e Identifying relation ship).when these
    two entitys are involved in many to many.(one column (primary key of main entity) is common in both the entitys).then what would be primary key of intersetion table or associative table.

    ex : A(projId) --> B(ProjId,P1)
    --->C(ProjId,P2)

    When B and C are involved in manay to many realteionship
    ProjId is common in both the entitys.


    (... ) is primary key


    Can any body explain what would be the scenario.

    Thanks
    Umashankar

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.


    Code:
    Table A 
      ProjID int IDENTITY,
      ProjName
    
    
    Table B
      ProjID int,
      TaskID int IDENTITY,
      TaskName
    
    Table C
      ProjID int,
      CostID int IDENTITY,
      CostName
    If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

    Regards,

    hmscott

  3. #3
    Join Date
    Feb 2004
    Posts
    3

    Database Design

    Thanks hmscott.

    I am 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














    Originally posted by hmscott
    I may be mis-interpreting your question, but I think the answer is that there is no relationship between Tables B and C outside of the relationship that exists through Table A. I guess you could say that there would be a Many-to-Many relationship between B and C, but I don't think that it would be very useful to any kind of informative analysis (short of alway using SELECT DISTINCT on the tables). Any kind of JOINs that you are going to do between B and C are always going to have to include A.


    Code:
    Table A 
      ProjID int IDENTITY,
      ProjName
    
    
    Table B
      ProjID int,
      TaskID int IDENTITY,
      TaskName
    
    Table C
      ProjID int,
      CostID int IDENTITY,
      CostName
    If this doesn't answer your question, perhaps you could rephrase it or include more detail in your DDL post.

    Regards,

    hmscott

Posting Permissions

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