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

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




"Chris" <kns@shdv.sd> wrote in message
news:OOApia8wHHA.424@TK2MSFTNGP06.phx.gbl...
> 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
>
>

On 11 Jul, 15:28, "Uri Dimant" <u...@iscar.co.il> wrote:
> 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
>
> "Chris" <k...@shdv.sd> wrote in message
>
> news:OOApia8wHHA.424@TK2MSFTNGP06.phx.gbl...
>
>
>
> > 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- Hide quoted text -
>
> - 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>
> > table 'projectdetails' (details of projects) and one or more result tables
> > (one per project) like 'projectresult1', 'projectresult2' etc ...
</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

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



<jhofmeyr@googlemail.com> schreef in bericht
news:1184166313.997291.317940@k79g2000hse.googlegr oups.com...
> On 11 Jul, 15:28, "Uri Dimant" <u...@iscar.co.il> wrote:
>> 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
>>
>> "Chris" <k...@shdv.sd> wrote in message
>>
>> news:OOApia8wHHA.424@TK2MSFTNGP06.phx.gbl...
>>
>>
>>
>> > 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- Hide quoted text -
>>
>> - 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>
>> > table 'projectdetails' (details of projects) and one or more result
>> > tables
>> > (one per project) like 'projectresult1', 'projectresult2' etc ...
> </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
>