Compound primary key table with subtypes

Me and a database architect were having argument over if a table with a compound primary key with subtypes made sense relationally and if it was 开发者_如何转开发a good practice.

Say we have two tables Employee and Project. We create a composite table Employee_Project with a composite primary key back to Employee and Project.

Is there a valid way for Employee_Project to have subtypes? Or can you think of any scenario where a composite key table can have subtypes?

To me a composite key relationship is a 'Is A' relationship (Employee_Project is a Employee and a Project). Subtypes are also a 'Is A' relationship. So if you have a composite key with a subtype its two 'Is A' relationships in one sentence which makes me believe this is a bad practice.

Employee-project is a bit hard, but one can imagine something like this -- although I'm not much of a chemist.

Or something like this, which would require different legal forms (fields) for single person ownership vs joint (time-share).

Or like this, providing that different forms are needed for full time and temp.

Employee projects have subtypes if the candidate subtypes are

• not utterly different, but
• not exactly alike

That means that

• Every employee project has some attributes (columns) in common. So they're not utterly different.
• Some employee projects have different attributes than others. So they're not exactly alike.

The determination has to do with common and distinct attributes. It doesn't have anything to do with the number of columns in a candidate key. Do you have employee projects that are not utterly different, but not exactly alike?

The most common business supertype/subtype example concerns organizations and individuals. They're not utterly different.

• Both have addresses.
• Both have phone numbers.
• Both can be plaintiffs and defendants in court.

But they're not exactly alike.

• Individuals can go to college.
• Organizations can have a CEO.
• Individuals can get married.
• Individuals can have children.
• Organizations (in the USA) can be liquidated.

So you can express individuals and organizations as subtypes of a supertype called, say, "Parties". The attributes all the subtypes have in common relate to the supertype.

• Parties have addresses.
• Parties have phone numbers.
• Parties can be plaintiffs and defendants in court.

Again, this has to do with attributes that are held in common, and attributes that are distinct. It has nothing to do with the number of columns in a candidate key.

To me a composite key relationship is a 'Is A' relationship (Employee_Project is a Employee and a Project).

Database designers don't think that way. We think in terms of a table's predicate.

If an employee can have many projects and a project can have many employees it is a many-to-many join that RDBM's can only represent easily in one way (the way you have outlined above.) You can see in the ER diagram below (employee / departments is one of the classic many-to-many examples) that it does not have a separate ER component. The separate table is a leaky abstraction of RDBMS's (which is probably why you are having a hard time modeling it).

http://www.library.cornell.edu/elicensestudy/dlfdeliverables/fallforum2003/ERD_final.doc

Bridge Entities

When an instance of an entity may be related to multiple instances of another entity and vice versa, that is called a “many-to-many relationship.” In the example below, a supplier may provide many different products, and each type of product may be offered by many suppliers:

While this relationship model is perfectly valid, it cannot be translated directly into a relational database design. In a relational database, relationships are expressed by keys in a table column that point to the correct instance in the related table. A many-to-many relationship does not allow this relationship expression, because each record in each table might have to point to multiple records in the other table.

http://users.csc.calpoly.edu/~jdalbey/205/Lectures/ERD_image004.gif

Here they do not event bother with a separate box although they add in later (at this step it is a 'pure' ER diagram). It can also be explicitly represented with a box and a diamond superimposed on each other.