Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Projects, tasks, persons...

    Hey my first post (yay!)

    Here is my problem. I'm currently working on a database for a person project and I don't figure how to construct my relational model for that :

    - A task can be a person task or a project task.
    - A task can be a project task affected to a person registered to a project.
    - If possible, I don't want to created separated table for the same data (task).

    If somone has idea...
    Attached Thumbnails Attached Thumbnails tasks.PNG  

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    We don't really have enough information to go on:
    * Is a task always either for a person or for a project (never both)?
    * Can a task be associated with more than one person, or more than one project?

  3. #3
    Join Date
    Jun 2007
    Posts
    5
    Sorry for the missing information

    * Is a task always either for a person or for a project (never both)?

    Yup, never both.

    * Can a task be associated with more than one person, or more than one project?

    A task can be associated with more than one person or more than one project.

    Thanks for the help.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    OK, then to preserve a single Tasks table you could do this:

    * Add a column to Tasks called TaskType, constrained to be 'PERSON' or 'PROJECT'.
    * Create a UNIQUE constraint on Tasks(TaskType,Id)
    * Create a table PersonTasks like this:

    create table PersonTasks
    ( PersonId REFERENCES Person
    , TaskType varchar(6) CHECK (TaskType = 'PERSON')
    , TaskId integer
    , FOREIGN KEY (TaskType,TaskId) REFERENCES Tasks(TaskType,Id)
    );

    * Create a similar table ProjectTasks

    Now when you create an association between a Person and a Task, you will only be allowed to choose 'PERSON' Tasks:

    i.e. this will work:
    Code:
    insert into PersonTasks(personId,TaskType,TaskId) values (1,'PERSON',101);
    but this will not (prevented by the CHECK constraint):
    Code:
    insert into PersonTasks(personId,TaskType,TaskId) values (1,'PROJECT',202);

  5. #5
    Join Date
    Jun 2007
    Posts
    5
    Thanks you a lot!

  6. #6
    Join Date
    Jun 2005
    Posts
    79
    Hi,

    I think I have the same problem but mysql 5.0 doesn't support the CHECK constraint.
    I have an "InvoiceLine" that contains a "Product" that can either be of type "A" or "B".
    Tables "A" and "B" have a totally different structure.
    I guess I also need to create two linking tables, "Product_A" and "Product_B" and create a field ProductType in the "Product" table.

    Would the table structure for the two linking tables be the same as with the solution above? Can this work without the CHECK constraint?

    Thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if A and B are totally different, then InvoiceLine should contain two FKs, both of which should be NULLable, and only one of which will actually contain a value on any given row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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