This is a db design question:

I am creating a db of projects that contains a table of users/people, companies and projects where

a user can be a child of the company table and and a project is a child of either a user or a company.

This clearly does not work in practical terms but I cannot see how to solve this, do I need some sort of intermediate table to link it all ?

How about following?

User (user_id,...company_id,..)
Company(company_id,..)
project(project_id,..., parent_id, parent_type)

parent_type is indicator which will store e.g. 'C' for company , 'U' for user

I get where you are coming from sushant but how would you then retrieve the data

I had thought about doing:

SELECT * FROM user,company WHERE user_id = ? OR company_id = ?

But it is then possible, in fact likely that the query would return two records only one of which would be correct as there is no constraint between the id's of the user and company tables making the id's unique

What should be the output of the query?

I want to be able to get the id and name of the owner of the project and also which table it came from.

if entity C is a child of either parent P1 or parent P2, then P1 and P2 are instances of a subtype of some larger supertype SP

the supertype SP will have a primary key, which both P1 and P2 will carry as foreign keys

C will then also carry the foreign key to the supertype SP

thus you can left outer join C to both P1 and P2 and be assured that you will get only one of them

well, that's the theory, anyway

in this particular example, with P1 actually being a child of P2 (which raises the thorny side issue of whether you can have the same userid in different companies), i would just put a two foreign keys to userid and companyid, into the project table

"parent type" column is not required, since whenever you retrieve a project, you will probably want to do a double outer join anyway -- if it's owned by a user, you'll want that user's company info anyway, and it it's owned by a company, the userid foreign ket in the project table will be null, so you will only get company

Thanks Rudy,

So basically are you moving what would be the PK's of the P1 and P2 tables in to a parent table in order to keep them unique?

The affect of this being when you come to insert a new record into either P1 or P2 you would first create a new record in SP get the new identity created and insert that into your new P1 or P2 record as a FK?

yes, that's right, that would be the way, using a supertype table

but like i said, in your example, i might not need it

companies
11 acme widgets
12 gizmos-r-us
13 thingamabobs inc

users
937 11 curly
936 11 larry
935 11 moe
931 13 tom
932 13 dick
933 -- harry

projects
xp3 --- 11 someacmeproject
xp3 937 11 acmeprjectownedbycurly
abc 933 -- harryprojectnotrelatedtoacompany


since a join from users to companies (going "up") is one-to-one, you can join the project to both users and companies

for a bit of consistency checking, you might want to declare a constraint that if the user fk in projects is not null, then the userid/companyid pair must exist in the users table, i.e. you can't have a project for a user at a company other than that user's company

Thanks y'all, I think I've got a grip on this now