10

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!

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
NightHawk
  • 3,633
  • 8
  • 37
  • 56
  • Relational databases suck big time at this. gl hf, as they say. – zneak Apr 29 '11 at 04:42
  • So what you are saying is that there isn't a "nice" solution to this? – NightHawk Apr 29 '11 at 04:44
  • I don't know any, at least. (Please put @zneak in your comment if you want me to comment back; otherwise I don't get notified) – zneak Apr 29 '11 at 04:45
  • @zneak What if I had the USER table, that contained a FK to a bridge table and in that bridge table I listed the PK of the USER table and in another field I had the CAT table name? I probably wouldn't be able to create one query, but with some PHP, I could get the results I need... what do you think? – NightHawk Apr 29 '11 at 15:08
  • See also my answer to https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m/695860#695860 – Bill Karwin Oct 02 '17 at 18:49
  • PostgreSQL tried to implement table inheritance, but in many ways it's totally broken. https://www.postgresql.org/docs/current/static/ddl-inherit.html (see the caveats section on that page). – Bill Karwin Oct 02 '17 at 18:51

2 Answers2

5

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.

Andy White
  • 86,444
  • 48
  • 176
  • 211
  • 1
    So if a user logs in and I query the USER table, how do I know which one of the other tables to query to get the remaining data? – NightHawk Apr 29 '11 at 04:55
  • The foreign key would only exist in one of the 2 tables. With a Join statement it would link with the correct table and then you can go from there. – Nick B May 11 '19 at 14:01
  • Is this not slow b/c you have to query all the tables in order to find out which child table has the relevant data? – Foobar Apr 17 '22 at 02:45
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.

user3124101
  • 21
  • 1