0

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.

Will
  • 918
  • 5
  • 12

3 Answers3

2

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

enter image description here

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

enter image description here

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

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • can you help me on this? https://dba.stackexchange.com/questions/177439/unique-many-to-many-relationship-through-sub-superclass-conjunction-tables – Yosua Lijanto Binar Jun 29 '17 at 18:23
1

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • I have no clue what you are saying. – Stephanie Page Feb 24 '11 at 19:51
  • @Stephanie Page: I edited my answer and provided a common business example. If you have specific questions, I'll do my best to answer them. – Mike Sherrill 'Cat Recall' Feb 24 '11 at 21:28
  • Thanks for your reply. You are saying it is relationally correct to have a composite relationship with a subtypes? Or are you saying a composite table can be correctly modeled with subtypes? – Will Mar 02 '11 at 02:42
  • I think it's easy to imagine that an employee project for buying commercial time during televised football games might have some different attributes from an employee project for building a web site. And I think it's easy to imagine that those two employee projects also have some attributes in common. And, yes, what I've described here and above is from a relational database designer's point of view, not from an object-oriented programmer's point of view. When you're looking at tables, you'd be well served to think in terms of predicates; predicates are more expressive than "is_a" and "has_a". – Mike Sherrill 'Cat Recall' Mar 02 '11 at 05:52
0

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.gifenter image description here

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.

nate c
  • 8,802
  • 2
  • 27
  • 28
  • Sorry for my late reply but is this answering my question? I don't follow. I understand this ER diagram, I understand this concept of a bridge entity but I guess my question in your case can a bridge entity have children entities(subtypes)? – Will Mar 02 '11 at 02:34