i have several different types of users use system. users need store such things username, password, email address, etc, if user of category a, need store values fields a, b, , c, if user of category b, need store values fields d, e, f, , g.
user ------- id username password cat_a -------- id b c cat_b -------- id d e f g i need use bridge table of sort link user 1 of cat tables, how go doing that? can't use this:
extend -------- user_id cat_id because wouldn't know cat table cat_id refers to. need field each of categories? if so, doesn't seem normalized since there lot of empty fields, if have 3 categories.
extend -------- user_id cat_a_id cat_b_id ... any input appreciated!
-ryan
there couple common ways map hierarchy in sql. because sql not have natural way handle inheritance, each of these conventional methods have own pros , cons. few of common ones are: table-per-hierarchy, table-per-type, , table-per-concrete-type, there several others. here's example of table-per-type model (each type in code maps directly table):
user --------------- user_id (pk, auto-generated?) username password categoryauser --------------- user_id (pk, fk user.user_id) b c categorybuser --------------- user_id (pk, fk user.user_id) e f g h to category users, select user inner join categoryauser. same thing category b users. model may or may not fit needs. if doesn't suggest search other types of models mentioned above.
Comments
Post a Comment