0

Say I have a table called People into which people are inserted from the moment they are born. Now lets say I want some subclasses of people based on their job. So there are also tables for Plumber, Carpenter and Electrician. Now, when a person is born, I don't know if they're going to grow up to be plumbers, or carpenters, or electricians - So I can't have reference the person in every table via a foreign key.

So when little Johnny grows up, how can I bind him to being a Plumber. Is there some sort of pattern for this, or is this a stupid question with an obvious answer? Either way I'd appreciate some help, thanks!

Improve this question
4
  • 1
    Party-Role-Relationship is one of the patterns that will do the job. "Plumber", "Carpenter", etc are all roles that each person may be assigned to for a certain period of time . – a1ex07 Mar 13 '15 at 16:56
  • ...I've had a go at finding some material on Party-Role-Relationship and turned up nothing, do you know of any good resources? – J Smith Mar 13 '15 at 17:27
  • Not sure if there is an online version, but search for "The Data Model Resource Book" by Len Silverston. – a1ex07 Mar 13 '15 at 17:35
  • 1
    Only use Inheritance for IS AND ALWAYS WILL BE relationship. Use Composition (hasMany) for PLAYS THE ROLE OF relationships – Neil McGuigan Mar 14 '15 at 18:08
2

Try searching "single table inheritance" or "class table inheritance". Especially the web pages from Martin Fowler.

Also, if you use a technique called "shared primary key", you can reference a person by that person's personid regardless of what occupation the person eventually chooses. In the Plumber table, the primary key is not plumberid, but rather personid, which is a copy of the personid from persons. You declare plumber.personid to be a primary key, and also to be a foreign key that references person.personid.

This has some rather nice results. It makes joining plumbers and persons simple, easy, and fast. It maintains the one-to-one or zero-to-one relationship between plumbers and persons. And it allows you to reference a child without knowing whether that child will grow up to be a plumber or not.

Improve this answer
0

It seems to me the this is a zero to many relationship. It could be that you need one primary key with ansi date of birth as part of it (pk,fk) and then associate this with the age on an occupation or position table. Or have two separate tables.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.