Hi,

I'm planning a site using cakephp and this site will be used to manage projects.

I need some ideas to design the database, especially the relations and teams that will be part of each project.

The vertical entities are:
- Director
- Zone Manager
- Local Manager
- Project Manager

The transversal entities will be:
- Consultant
- Assistant

Each project will be seen vertically by the Managers that have people in each team.
The Consultant A will part of project A and B and Consultant B will be part of project C and D, etc. The Assistants will work in a Pool, so like Consultants they will be part of a team assigned to a project.

Using the best practices what is the best way to associate entities to a team and to each project?

Thanks

NN

Moved to Database Concepts & Design topic.

Can Consultant A and Consultant B be part of project A?
If so you have a many-to-many relationship.

If you can only have one consultant per project then it is one-to-many.

Hi,

Yes, we may have more than one Consultant in the same project.
I would like to design the table to be simple and logical.

Here is my idea with just some fields from the tables:

Project Table
|id|name|...|

Users Table:
|id|name|function|...|

Team Table (This is the table where relations are established) :
|proj_id|users_id|

Sample Team Fields:
|1|1,4,6|
|2|1,6,10,21|

My questions:

Is this a good practice?
I can place users_id inside an PHP array a search for the users in each project. Is this a good way to go?

How would I manage vertical relations so that Local or Zone Manager only have access projects?

Thanks

NN

your Project and Users tables are correct

the Team table should have two integers only -- your example shows a comma-delimited list of users, and that's wrong, you want one row per project/user

Hi r937,

That's a great suggestion.

Team Fields:

|proj_id|users_id|
|1 |1 |
|1 |5 |
|1 |7 |
|2 |1 |
|2 |10 |
....

Is this correct?

How about the hierarchic association?

Thanks

nfn

yes, that's the way

as for the hierarchy, use the adjacency model

see Categories and Subcategories -- it's the same structure

Thanks...

That's a good start.