I have a problom about my ERD...
I have 3 user table with different attributes. Each user connected to house table. Then house table has many logs. My question is, how to know the logs are related to users depends on user type
Asked
Active
Viewed 464 times
-1
-
1If you are designing your structure then instead of 3 user tables create one user table with type column and associate user id with home – M Khalid Junaid Sep 21 '14 at 08:00
-
but those 3 users has different attributes on each table.. – geralvin Sep 21 '14 at 08:24
1 Answers
0
Add a USER table bridging the USER_x tables to HOUSE:
-- [id] is a type x user and ...
USER_x(id,...) PK (id) FK (id) REFERENCES USER (id)
-- [id] is a user
USER(id) PK (id)
-- house [id] is used by [USER_id] and ...
HOUSE(id,USER_id,...) PK (id) FK (USER_id) REFERENCES USER (id)
There are various ways to constrain this kind of design so that USER has exactly the disjoint union of USER_x ids. Eg a USER type discriminator column whose values are the various values for x. (Also other kinds of design. Eg a nullable USER foreign key per USER_x. With their own constraints. Eg checking that only one such FK IS NOT NULL.)
Search for Stackoverflow SQL articles re subtables, subtypes, inheritance and foreign keys. Eg this or this.
Here's how to constraint this kind of situation without triggers.
