MySQL inheritance?

[ThatStupidAche]

Does MySQL support entity inheritance (i.e. to implement an "is-a" relationship)?

I am trying to implement a hierarchial privileges system that looks something like this:

• a Coordinator is-a Member but with more privileges

• a Manager is-a Coordinator but with more privileges

• an Admin is-a Member but with more privileges

The behaviour that I'm looking for is that each child entity (e.g. Coordinator) has both its own distinguishing attributes and relationships (e.g. the relationship Coordinates), plus attributes/relationships that belongs to the parent (e.g. those of the Member entity).

Is there a direct way to achieve this in MySQL (I heard that for some RDBMS you can do so by declaring that a table inherits from another table when you create the child table)? If not, is there any possible work-arounds?

[tomhath]

You've run into a fairly common problem. You are thinking in OO terms, but a relational database is based on set logic. Try thinking of it this way:

Coordinators are a subset of Members. Some additional information (privileges) is associated with a Member who is also a Coordinator. So the Coordinators table has a link (foreign key) back to it's parent row in the Members table. To find everything about a Coordinator you perfom a JOIN on the Members table with the Coordinators table (which will give you the subset of Members you are looking for, with their additional attributes). Manager is a subset of Coordinator, Admin is a subset of Manager. 😃