Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    4

    Help with database design.

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Moved to Database Concepts & Design topic.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    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.
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2007
    Posts
    4
    Quote Originally Posted by georgev
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2007
    Posts
    4
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's the way

    as for the hierarchy, use the adjacency model

    see Categories and Subcategories -- it's the same structure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2007
    Posts
    4
    Thanks...

    That's a good start.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •