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
UmashankarI 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.

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|||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.

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