"is-a" relationship?

[UmiSal]

How can you implement an "is-a" relationship in MySQL?


For example: a Car [i]is-a[/i] Vehicle, i.e. it has all of Vehicle's attributes (e.g. id) and is part of every relationship that Vehicle is part of (e.g. [i]Moves-to[/i]), plus new attributes and execlusive relationships of its own.

I want to know this because I am trying to implement a hierarchial user privileges system that looks something like this:
[list][*]a [b]Coordinator [i]is-a[/i] Member[/b] but with more privileges[*]a [b]Manager [i]is-a[/i] Coordinator[/b] but with more privileges[*]an [b]Admin [i]is-a[/i] Member[/b] but with more privileges[/list]

Where bold words are entities, and italic words are relationships.

How can I implement this in MySQL? Is there a direct way to do it?

Generally in this database design, each child entity (i.e. Coordinator, Admin and Manager) doesn't offer much new attributes (aside from the ones it inherits from its parent), but each one of them is involved in some execlusive relationships. Should I substitute the Coordinator, Manager and Admin entities with an attribute in the Member table indicating privileges mode (for e.g. members with "mode" value = "admin" are actually admins)? This will cause the Member entity to be involved in ALL the relationships that would otherwise be a child entity execlusive (e.g. Coordinator [i]Coordinates[/i] Area will now become Member [i]Coordinates[/i] Area). Should I rely on PHP to enforce only Members with certain "mode" value to participate in the otherwise exclusive relationships?

Please Help! I'm desperate :(

[wickning1]

I'm not sure I understand your dilemma. You can format the data any way you like as long as it's not duplicate.

Personally, I would create a table for every class, that stores its unique member variables. Every object that "is-a" instance of that class has a row in that table. (So, instances of any subclass have a row too.)

If a class has known children I create one column that specifies the row's subclass (NOT necessarily the row's "real" class). Then in the subclass table there should be a column that can link back to the correct row in the parent table. If the subclass has children, it has a subclass column too, etc.

It sounds like you don't have very many, or maybe no unique member variables for a lot of your classes. I would say that it doesn't matter. You never know when you may need to add them, that's the whole point of object-oriented design. Your database design should be just as flexible as your code design.

[UmiSal]

The thing is, I am not speaking about any OO design, my problem is a strictly database problem. Sorry if I didn't make that clear. Even though I am using OO PHP to manipulate the database data, there are no classes corresponding to any entities that I mentioned.

Basically, what I'm trying to do is to have a table obtain another table's attributes/involvement in relationships, and then add new specialized attributes/involvement in relationships to the ones it obtained from the parent table.

[fenway]

That's not going to work -- databases are flat files with relationships, not classes -- and you can't "obtain" or "inherit" anything automatically.

The best way is not the have to concepts of your user levels in the actual DB design -- store the actual privileges in one table, mark the "user level" in the users table, and then have to your app logic automatically create the necessarily permissions.