How to emulate "class extension" database tables?

Question

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

USER ------- id username password CAT_A -------- id a b c CAT_B -------- id d e f g 

Most likely, I need to use some kind of bridge table to associate the user with one of the CAT tables, but how can I do this? I can not use something like this:

 EXTEND -------- user_id cat_id 

Because I don’t know which CAT table the cat_id refers to. Do I need a field for each of the categories? If so, this does not seem to be normalized, as there will be many empty fields, especially if I have 3 categories.

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

Any entry is welcome!

Answer 1

There are several general ways to map hierarchies in SQL. Since SQL does not have a natural way to handle inheritance, each of these common methods has its pros and cons. A few are table-per-hierarchy, table-per-type and table-per-specific-type, but there are probably a few others. Here is an example of a table model for one type (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 Category A users, select from User inner join CategoryAUser. The same is for category B users. This model may or may not meet your needs. If this is not the case, I would suggest looking for other types of models mentioned above.

Answer 2

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

You can add a column to your USER table, which indicates which category the user belongs to. Then, when you query a custom table, you can base a category query on it.

Alternatively, you can “leave the outer join” of all category tables and base your result on which columns you will return. In this case, you will get your USER columns and all a, b, c, d, e, f and g. Some of these columns would be null, of course, if there were no entries in this table for this user.