good practise (2)

[Chris]

Hi again,

I need another good practise advice.

Everybody (100 persons) in our compagny manages one or more projects. Each
projects needs two tables: table 'project' (general information), table
'projectdetails' (details of projects) and one or more result tables (one
per project) like 'projectresult1', 'projectresult2' etc ...
One project needs more or less 300 records with 6 fields (with size:
nvarchar(200) average)). Nothing special.

There are more scenarios possible (using sql server 2005 express):

1) one database ('project') containing one table 'project', one table
'projectdetails' and several tables 'projectresultX', common to all
projectmanagers.

2) one database ('project') containing for each projetcmanager a table
'project_managerID', a table 'projectdetails_ managerID' and several tables
'projectresultXmanagerID'.

3) for each projectmanager a database 'projectmanager_ID' containing one
table 'project', one table 'projectdetails' and several tables
'projectresultX'.

I must say: the databases and tables are all created programmatically in
code-behind when the projectmanager starts the application the first time
only.

So which scenario woul be the best generally spoken?

Thanks for your advice.
Chris

 

[Uri Dimant]

Chris
1) Table for Project Managers
2) Table for Projetcs (projectid ,managerid.....)
3) Table for Prioject Details
4) Table for Project_Results (junction table (many-to-many relationship))
(projectid, project_result_id ......) Could be PK on projectid,
project_result_id

 

[jhofmeyr]

Chris
1) Table for Project Managers
2) Table for Projetcs (projectid ,managerid.....)
3) Table for Prioject Details
4) Table for Project_Results (junction table (many-to-many relationship))
(projectid, project_result_id ......) Could be PK on projectid,
project_result_id















- Show quoted text -

As Uri suggested, having multiple projectresults tables is seldom the
best design, however the wording of your question was a little
unclear:

</quote>

Do you mean that for each project there is a single results table, but
that each manager could have more than 1 results table (due to the
fact that they can run more than 1 project)?
If this is the case, then you should not need a many-to-many
relationship as the relationship between project and projectresult is
only one-to-many (projectid column could be added to the
projectresults table and allow multiple references to the same
project).

If (as Uri assumed) 1 project could require multiple projectresults
tables then you will need a junction table to facilitate the many-to-
many relationship

J

 

[Chris]

Thanks for replying.
The answer to this is yes:
"Do you mean that for each project there is a single results table, but
that each manager could have more than 1 results table (due to the
fact that they can run more than 1 project)?"

The tables 'results' are all diffeent (different fields).

To summarize:
one database 'project' (and not a database per manager?)
one table 'managers' for all managers and projects'
one table projectdetails for all managers and all projects
several tables 'results' for each project of each manager

This is ok?

What's the drawback of the two other scenarios?
one common database and for each manager one table 'project', 'details'
'results
one database for each manager

Thanks