This is my first post on the forum. I've had some basic experience with database design, but I've run into an issue I need help wrapping my head around.

Here it is:
I have constructions projects (Projects table) that have a customer. Sometimes that customer is an individual (John Smith) and sometimes the customer is a company (Acme Property Management). For each project there is only 1 project holder- a person or a company. The distinction is important to me.

Originally I thought the only way might be to do this programatically and make a system. For example, all company_id fields are 1-10000, and all person_id fields are 10001 - ?. Then if the project's project_holder_id field is 1-10000 I know to look in the Companies table instead of the Persons table.

I'd like to handle this more elegantly and without having to rely on programming. Any thoughts? Thanks in advance.

do a search on supertype/subtype

the supertype table would be customers, and the subtype tables would be individuals and companies, each with a foreign key primary key back to the customers table

then the customers table participates in relationships with projects, etc.

I like the title of the post ..almost made me think this forum as agony aunt forum :P

@r937:

Thanks for your help. The "supertype/subtype" search helped. I found a very useful the link you posted in another forum in case anyone else needs help on this subject.

http://www.cbe.wwu.edu/misclasses/mi...persubtype.ppt

@anyone:

For clarification.... how does the supertype_id extend through the heirarchy?

I know I need a PK for the supertype, such as Entity. Do the subtypes of Person and Company simply have FKs of person_entity_id and company_entity_id that also act as PKs for their own subtypes, such as employee_person_entity_id, etc.?

Thanks.

yes, that's it

Thanks for the link to the powerpoint presentation file.
It was quite useful, but I have a question related to this.
I have trouble translating an ER diagram to a DB model.

In the powerpoint presentation there is a slide with an Employee supertype with an Employee Type attribute and subtypes Employee Types themselves (Hourly, Salaried and Consultant), which is very similar to my design situation.

Suppose I wanted to somehow indicate if a Consultant is the Boss of the Hourly and/or Salaried Employee (either, none or both), how could that be designed in a database model?
I can put a relationship between them, but keep thinking that might not be the wisest solution....but I'm not sure why (integrity issues?)

Thanks in advance for any help.