How to emulate "extending a class" with database tables?

Question

I will have several different types of users that will use my system. For all users I need to store such things as username, password, email address, etc, but if they are a user of category A, I also need to store values for fields a, b, and c, but if they are a user of category B, I need to store values for fields d, e, f, and g.

USER
-------
id
username
password

CAT_A
--------
id
a
b
c

CAT_B
--------
id
d
e
f
g

I most likely need to use a bridge table of some sort to link a user to one of the CAT tables, but how do I go about doing that? I can't use something like this:

EXTEND
--------
user_id
cat_id

Because I wouldn't know which CAT table the cat_id refers to. Would I need a field for each of the categories? If so, that doesn't seem normalized since there would be a lot of empty fields, especially if I have 3 categories.

EXTEND
--------
user_id
cat_a_id
cat_b_id
...

Any input is much appreciated!

Answer 1

There are a couple common ways to map a hierarchy in SQL. Because SQL does not have a natural way to handle inheritance, each of these conventional methods have their own pros and cons. A few of the common ones are: table-per-hierarchy, table-per-type, and table-per-concrete-type, but there are probably several others. Here's an example of a table-per-type model (each type in your code maps directly to a table):

User
---------------
user_id (PK, auto-generated?)
username
password

CategoryAUser
---------------
user_id (PK, FK to User.user_id)
a
b
c

CategoryBUser
---------------
user_id (PK, FK to User.user_id)
e
f
g
h

To get all the category A users, do a select from User inner join CategoryAUser. Same thing for category B users. This model may or may not fit your needs. If it doesn't I would suggest search for other types of models mentioned above.

Answer 2

I know this thread is old, but I'm looking for an elegant solution myself. Based on what I've done in the past:

You could add a column to your USER table that tells what "category" the user belongs to. Then, when you query the user table, you can base your Category query on that.

Alternatively, you could "left outer join" all of the category tables and base your result on what columns you get back. In this case, you would get back your USER columns and all of a, b, c, d, e, f, and g. Some of those columns would be null, of course, if there were no entries in that table for that user.