Results 1 to 5 of 5

Thread: DB Design

  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Unanswered: DB Design

    Ok so I have the following tables (I am just starting so if there is a better design please let me know) I see having about 200 projects to track.

    Table Project
    ID primary key
    Name
    City
    State
    ClientID (note there can be more then one client so her is a problem)
    StaffID (note more then one staff is possible on project)
    notes

    Table Client
    ClientID (some how relate this to Project)
    Firstname
    lastname
    phone
    cell

    Table Staff
    StaffID (some how relate this to Project)
    Firstname
    lastname
    phone
    cell
    Department


    so what would be really helpfull is to know how to setup the above tables and to have some guidance on table joins and then the SQL statements


    Using Mysql and PHP4

    Thanks

  2. #2
    Join Date
    Feb 2003
    Posts
    3

    Re: DB Design

    What I see the problem being is that there can be more then 1 staff and client per project. You see what I am saying? The below only allows for 1 staff and one client which just wont do. Hum any thoughts cause I'm stuck.

    Originally posted by heartland
    Ok so I have the following tables (I am just starting so if there is a better design please let me know) I see having about 200 projects to track.

    Table Project
    ID primary key
    Name
    City
    State
    ClientID (note there can be more then one client so her is a problem)
    StaffID (note more then one staff is possible on project)
    notes

    Table Client
    ClientID (some how relate this to Project)
    Firstname
    lastname
    phone
    cell

    Table Staff
    StaffID (some how relate this to Project)
    Firstname
    lastname
    phone
    cell
    Department


    so what would be really helpfull is to know how to setup the above tables and to have some guidance on table joins and then the SQL statements


    Using Mysql and PHP4

    Thanks

  3. #3
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    You need two extra tables to resolve the many-to-many relationships you have.

    Table Project
    ID primary key
    Name
    City
    State
    Notes

    Table Client
    ClientID
    Firstname
    lastname
    phone
    cell

    Table Staff
    StaffID
    Firstname
    lastname
    phone
    cell
    Department

    Table ProjectClient
    ProjectID (Partial Primary Key/Foreign Key pointing to Project.ProjectID)
    ClientID (Partial Primary Key/Foreign Key pointing to Client.ClientID)

    Table ProjectStaff
    ProjectID (Partial Primary Key/Foreign Key pointing to Project.ProjectID)
    StaffID (Partial Primary Key/Foreign Key pointing to Staff.StaffID)

    Each project would have one or more entries in ProjectClient, one for each client. It would also have an entry in ProjectStaff for every staff member working on the project.

    Is the SQL statement guidance that you're looking for to do something like listing all of the staff on a project? Or were you looking for help with the DDL required to set the tables up? (Or both?)

  4. #4
    Join Date
    Feb 2003
    Posts
    3
    OK I see how that table design will work thanks

    So about writing the SQL statment

    Usually I would go (Select * from Project) or (Select * from Project were ID='1') but that wont just do as now there are joins I believe.

    So would someone please provide me with the SQL statment using the advised table design by bcrockett to:
    1. select * From all tables
    2. select one project with the client and staff for that project ie like (Select * From Table(s) where id='1')
    3. Update a client for a particular project
    3 update a staff for a particular project


    This help is much appricated

    Also what is ment by the partial and foreign key? I understand creating a primary how do I create the partial and foregin?
    ProjectID (Partial Primary Key/Foreign Key pointing to Project.ProjectID)

    Thanks

  5. #5
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    >1. select * From all tables

    That would just give you a dump of the entire database. I don't know what use that would be to anyone, but you could do it.

    >2. select one project with the client and staff for that project ie like (Select * From Table(s) where id='1')

    You'd probably use two statements for this in MySQL. (Other RDBMSs would allow you to use a UNION clause.)

    SELECT P.Name AS Project,
    CONCAT(C.Firstname, ' ', C.lastname) AS Client
    FROM Project P
    LEFT OUTER JOIN ProjectClient PC ON PC.ProjectID = P.ID
    WHERE P.ID = 1;

    SELECT P.Name AS Project,
    CONCAT(S.Firstname, ' ', S.lastname) AS Staff
    FROM Project P
    LEFT OUTER JOIN ProjectStaff PS ON PS.ProjectID = S.ID
    WHERE P.ID = 1;

    You probably want to use LEFT OUTER joins in case staff and/or clients have not yet been assigned to the project.

    >3. Update a client for a particular project

    Do you mean update their phone number, or change the client that is involved in the project?

    >3 update a staff for a particular project

    Just delete the row from ProjectStaff that is no longer involved in the project, and insert a new row for the new staff. If you want to maintain a history of which staff worked on a project, you could do it be adding 'start' and 'end' date columns to the ProjectStaff table.


    >Also what is ment by the partial and foreign key? I understand creating a primary how do I create the partial and foregin?

    A partial primary key is a primary key that is made up of multiple columns. The combination of ProjectID and ClientID, for instance, is unique (unless a client can be involved in the same project more than once).

    CREATE TABLE ProjectClient (
    ProjectID INT NOT NULL,
    ClientID INT NOT NULL,
    PRIMARY KEY (ProjectID, ClientID)
    );

    A foreign key forces a column in the table to match a column in another table. In MySQL, this only works IF YOU'RE USING InnoBD (and maybe BDB?) TABLES! I'd recommend that you look into building constraints into your database rather than rely on the application to enforce them.

    MySQL has been used for years without foreign keys. Programmers have just built data integrity constraints into the application instead.

Posting Permissions

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