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...

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?

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.

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:
but this will not (prevented by the CHECK constraint):

Thanks you a lot!

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

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