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

Design Question

Hi all,

I am currently building a project management application.
The users wants to save existing projects as templates.

My question is, what would be the better solution.
Create seperate tables to hold template information.
Or use the same table as the existing project but, flagged as Templates?My feeling is that having one table would be the better solution. Having 2 separate tables with the same file structure is asking for trouble maintenance-wise. Any schema changes would have to be done twice.

Terri|||Yes thats what the IT Manager said and I agree.
But the template data is only varchar name and description type stuff.
Where as the other table has datetime, bit, int etc type fields.
So if I combine the two table I will have to allow for null values.

Is this a problem?|||Well, now it is sounding like the template schema and the project schema are not the same. If you post the schema perhaps better advice can be offered.

And I don't see a problem with allowing for NULL values, as long as you account for them in your coding.

Terri|||Use the same table as your manager said. Fewer tables = simpler design = better program. Also you can easily change templates into project or vice versa if you ever see the need to. (Think easy template creation from existing campaigns?)|||Thanks guys|||Or you can normalize your project table into ProjectHeader and ProjectDetails. Include in the ProjectHeader those fields which are common to templates and projects, and add a ProjectType field to identify it as a project or template. The additional fields that only apply to projects can then be placed in your new ProjectDetails table, along with the foreign key reference to the ProjectHeader table.

This is generally how you would handle object generalizations (or inheritance, whatever terminology you want to use) like this. Shoving it all into one single table can cause problems later - what if you decide you now have more "special cases" of project records?