0

I have database for multi store application. So, I have 3 type of user thats Admin, Customer, Merchant and I have 3 table for users. User table, Customer table, Merchant table.

User Table;

id
username
name
password
email
status
city_id
state_id
role_id
...

Customer Table;

id
taxNumber
cart_id
user_id

Merchant Table;

id
taxNumber
commission
user_id

So, there is a 1:0 relationship between merchant/customer tables and user table. I want a 1:1 relationship but if I do this, I have to create columns like customer_id and merchant_id and if customer_id is not null then merchant_id will be null or merchant_id is not null then customer_id will be null in user table. Is it bad for normalization?

1
1

With the current design you could have several customers with the same user_id (similarly for merchants). This is a one-to-many association. To solve this problem, simply do not introduce id for table customers, but use user_id as primary key for it, in addition to it being a foreign key for table users. Then you can have only one customer for user. Similarly for table merchants.

Then you can enforce the constraint that users cannot exists without either being a customer or a merchant by defining in your application functions to create customers or merchants, but not users. Those functions will create the appropriate rows. Finally, is up to you to decide if a customer can be also a merchant or not. In both cases, the function to create the customer or the merchant must take appropriate action.

Not the answer you're looking for? Browse other questions tagged or ask your own question.