Inheritance in tables - structure problem
- by Naor
I have 3 types of users in my system. each type has different information
I created the following tables:
BaseUser(base_user_id, username, password, additional common data)
base_user_id is PK and Identity
UserType1(user_id, data related to type1 only)
user_id is PK and FK to base_user_id
UserType2(user_id, data related to type2 only)
user_id is PK and FK to base_user_id
UserType3(user_id, data related to type3 only)
user_id is PK and FK to base_user_id
Now I have relation from each type of user to warehouses table. Users from type1 and type2 should have only warehouse_id and users from type3 should have warehouse_id and customer_id.
I thought about this structure:
WarehouseOfUser(base_user_id,warehouse_id)
base_user_id is FK to base_user_id in BaseUser
WarehouseOfTyp3User(base_user_id,warehouse_id, customer_id)
base_user_id is FK to base_user_id in BaseUser
The problem is that such structure allows 2 things I want to prevent:
1. add to WarehouseOfTyp3User data of user from type2 or type1.
2. add to WarehouseOfUser data of user from type3.
what is the best structure for such case?
