Design table need help

Currently I need to design database table and got stuck.

I have a project and its sub projects. A parent project and its sub projects share the same requirement.The description of the requirement will be changed every day.

For example

Project A - Do Some thing 1 on Project A [09/28/2007]

Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]

Every time the comments updated, both project A and project A-A1 should share the same update i.e select project A and its subproject will be displayed

Project A Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]

Project A-A1 Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29//2007]

When you tried to insert new sub project, the requirement will be shared with the parent project ie.

Project A - Do Some thing 1 on Project A [09/28/2007]

Project A-A1 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007]

Project A-A2 - Do Some thing 1 on Project A [09/28/2007] , Do Some thing 2 on Project A [09/29/2007] , Do Some thing 2 on Project A-A2 [09/30/2007]

assume Project A-A1 and Project A-A2 are sub project of project A

Could anyone show me how to design the database table for those mess?

Thank you

You need 3 table

ProjectGroup

id

name

Project

id

groupID -> ProjectGroup.id

name

ProjectLog

id

groupID ->ProjectGroup.id

note

date_add

Regards,

Max

|||

My previous post don't actually work, my bad.

ProjectGroup

id

name

Project

id

groupID -> ProjectGroup.id

name

ProjectGroupLog

id

groupID ->ProjectGroup.id

note

date_add

ProjectLog

id

projectID ->Project.id

note

date_add

Now when you need to display the logs, just merge the ProjectGroupLog result with the ProjectLog.

|||

Isn't this a self-referential ParentID relationship? You can do it all in one table (I think):

tbProject

ProjectID int IDENTITY (1,1) (PK)

ProjectName nvarchar(255)

ParentProjectID int NOT NULL (FK --> projectID)

TopLevelProjectID int NOT NULL (FK --> ProjectID)

Requirement nvarchar(255)

Create a project, it gets ProjectID = 1, and since it has no Parent, it's ParentID = 1 that means that it's a Top-Level project, so it's TopLevelProjectID = 1.

A sub project would have ProjectID = 2 (or whatever the next avilable ID is), a ParentProjectID = 1, and a TopLevelProjectID = 1.

And, if this sub had a sub, then:

ProjectID = 3 (or next available ID)

ParentID = 2 ('cause that's one level UP from this project)

TopLevelProjectID = 1 ('cuase that the top of the tree from this project)

With this, you can use something like:

Update Set REquirment = Requirment + " SOME NEW STRING " where TopLevelProject = 1

Code Block

create table #Projects(

ProjectID int identity(1,1)

, ParentID int

, TopLevelID int

, ProjectName varchar(255)

, ProjectRequirement varchar(255)

)

insert into #Projects(ProjectName,ParentID,TopLevelID,ProjectRequirement)

select 'Project # 1', 1, 1,'some thing or other'

insert into #Projects(ProjectName,ParentID,TopLevelID,ProjectRequirement)

select 'Project # 2', 2, 2, 'do this now'

insert into #Projects(ProjectName,ParentID,TopLevelID, ProjectRequirement)

select 'Project # 3', 3, 3, ' FOOBAR the 3rd'

insert into #Projects(ProjectName,ParentID,TopLevelID)

select 'Project # 1: Sub A', 1, 1

insert into #Projects(ProjectName,ParentID,TopLevelID)

select 'Project # 2: Sub A', 2, 2

insert into #Projects(ProjectName,ParentID,TopLevelID, ProjectRequirement)

select 'Project # 1: Sub B', 1, 1, 'Hey, here''s another one!!'

insert into #Projects(ProjectName,ParentID,TopLevelID)

select 'Project # 1: Sub A: Sub i', 4, 1

insert into #Projects(ProjectName,ParentID,TopLevelID)

select 'Project # 1: Sub A: Sub ii', 4, 1

insert into #Projects(ProjectName,ParentID,TopLevelID, ProjectRequirement)

select 'Project # 1: Sub A: Sub ii: Sub a:', 8, 1, 'Do it. Do it now!'

insert into #Projects(ProjectName,ParentID,TopLevelID)

select 'Project # 3: Sub A', 3, 3

insert into #Projects(ProjectName,ParentID,TopLevelID)

select 'Project # 3: Sub A: Sub i', 10, 3;

with CTE

as

(

select ProjectName as [ProjectName],ProjectName as [ParentProject],ProjectName as [TopLevelProject], convert(varchar(4000),IsnUll(ProjectRequirement,'')) as [REQ], ProjectID from #Projects where TopLevelID = ProjectID

union all

select P.ProjectName,cte.[ProjectName],cte.[TopLevelProject], convert(varchar(4000),CTE.REQ + ': ' + isnull(ProjectRequirement,'')), p.ProjectID

from CTE inner join #Projects P on P.ParentID = cte.ProjectID

where P.ProjectID <> p.ParentID

and p.ProjectID <> p.TopLevelID

)

select * from CTE order by TopLevelProject, ParentProject

drop table #Projects

|||

You can accomplish this with 2 tables. Parent Table and Child Table or Project Table and Sub Project Table.

Project Table

-->ProjectNo

-->Comment1

-->Comment2

SubProject Table

-->ProjectNo FK

-->SubProjectNo

-->SubComment1

-->SubComment2

Your update on your SubProject would reflect in the Project as follows:

UPDATE sp

SET sp.SubComment1 = 'Did some work', p.Comment1 = 'Did some work on SubProject' & 'A1'

FROM SubProject sp

JOIN Project p on p.ProjectNo = sp.ProjectNo

WHERE sp.SubProjectNo = 'A1' AND p.ProjectNo = 1

As far as your insert statement is concerned, you would use an ON INSERT trigger on the Project Table to create a subrecord in the subproject table.

Adam