Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider the following:

entity User
{
    autoincrement uid;
    string(20) name;
    int privilegeLevel;
}

entity DirectLoginUser
{
    inherits User;
    string(20) username;
    string(16) passwordHash;
}

entity OpenIdUser
{
    inherits User;
    //Whatever attributes OpenID needs... I don't know; this is hypothetical
}

The different kinds of users (Direct Login users, and OpenID users) display an IS-A relationship; namely, that both types of users are users. Now, there are several ways this can be represented in an RDBMS:

Way One

CREATE TABLE Users
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    type ENUM("DirectLogin", "OpenID") NOT NULL,
    username VARCHAR(20) NULL,
    passwordHash VARCHAR(20) NULL,
    //OpenID Attributes
    PRIMARY_KEY(uid)
)

Way Two

CREATE TABLE Users
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privilegeLevel INTEGER NOT NULL,
    type ENUM("DirectLogin", "OpenID") NOT NULL,
    PRIMARY_KEY(uid)
)

CREATE TABLE DirectLogins
(
    uid INTEGER NOT_NULL,
    username VARCHAR(20) NOT NULL,
    passwordHash VARCHAR(20) NOT NULL,
    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid) REFERENCES Users.uid
)

CREATE TABLE OpenIDLogins
(
    uid INTEGER NOT_NULL,
    // ...
    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid) REFERENCES Users.uid
)

Way Three

CREATE TABLE DirectLoginUsers
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    username VARCHAR(20) NOT NULL,
    passwordHash VARCHAR(20) NOT NULL,
    PRIMARY_KEY(uid)
)

CREATE TABLE OpenIDUsers
(
    uid INTEGER AUTO_INCREMENT NOT NULL,
    name VARCHAR(20) NOT NULL,
    privlegeLevel INTEGER NOT NULL,
    //OpenID Attributes
    PRIMARY_KEY(uid)
)

I'm almost certain the third way is the wrong way, because it's not possible to do a simple join against users elsewhere in the database.

My real world example is not a users with different logins example though; I'm interested in how to model this relationship in the general case.

share|improve this question
    
I've edited my answer to include the approach suggested in the comments by Joel Brown. It should work for you. If you're looking for more suggestions, I would re-tag your question to indicate you are looking for a MySQL-specific answer. –  Nick Chammas Sep 12 '11 at 18:17
add comment

2 Answers

Way two is the correct way.

Your base class gets a table, and then child classes get their own tables with just the additional fields they introduce, plus foreign key references to the base table.

As Joel suggested in his comments on this answer, you can guarantee that a user will have either a direct login or an OpenID login, but not both (and also possibly neither) by adding a type column to each sub-type table that keys back to the root table. The type column in each sub-type table is restricted to have a single value representing the type of that table. Because this column is foreign keyed to the root table, only one sub-type row can link to the same root row at a time.

For example, the MySQL DDL would look something like:

CREATE TABLE Users
(
      uid               INTEGER AUTO_INCREMENT NOT NULL
    , type              ENUM("DirectLogin", "OpenID") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
);

CREATE TABLE DirectLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("DirectLogin") NOT NULL
    // ...

    , PRIMARY_KEY(uid)
    , FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

CREATE TABLE OpenIDLogins
(
      uid               INTEGER NOT_NULL
    , type              ENUM("OpenID") NOT NULL
    // ...

    PRIMARY_KEY(uid),
    FORIGEN_KEY (uid, type) REFERENCES Users (uid, type)
);

(On other platforms you would use a CHECK constraint instead of ENUM.) MySQL supports composite foreign keys so this should work for you.

Way one is valid, though you are wasting space in those NULL-able columns because their use depends on the type of user. The advantage is that if you choose to expand what kinds of user types to store and those types don't require additional columns, you can just expand the domain of your ENUM and use the same table.

Way three forces any queries that reference users to check against both tables. This also prevents you from referencing a single users table via foreign key.

share|improve this answer
    
How do I deal with the fact that using way 2, there's no way to enforce that there is exactly one corresponding row in the two other tables? –  Billy ONeal Sep 7 '11 at 20:45
1  
@Billy - Good objection. If your users can only have one or the other, you can enforce this via your proc layer or triggers. I wonder if there is a DDL-level way to enforce this constraint. (Alas, indexed views do not allow UNION, or I would have suggested an indexed view with a unique index against the UNION ALL of uid from the two tables.) –  Nick Chammas Sep 7 '11 at 20:55
    
Of course that assumes that your RDBMS supported indexed views in the first place. –  Billy ONeal Sep 7 '11 at 21:06
1  
A handy way to implemet this kind of cross-table constraint would be to include a partitioning attribute in the super-type table. Then each sub-type can check to ensure that it relates only to super-types that have the appropriate partitioning attribute value. This saves having to do a collision test by looking at one or more other sub-type tables. –  Joel Brown Sep 7 '11 at 21:21
1  
@Joel - So, for example, we add a type column to each sub-type table that is restricted via CHECK constraint to have exactly one value (that table's type). Then, we make the sub-table foreign keys to the super-table into composite ones on both uid and type. That's ingenious. –  Nick Chammas Sep 7 '11 at 21:25
show 7 more comments

They would be named

  1. Single Table Inheritance
  2. Class Table Inheritance
  3. Concrete Table Inheritance.

and all have their legitimate uses and are supported by some libraries. You have to find out which fits best.

Having multiple tables would make take the data management more to your application code but would reduce the amount of unused space.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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