primary key referened by only single foreign key of another table among multiple tables that can reference it
0
votes
1
answer
52
views
Let's say I have table Account that contains username and password of users to login:-
account (
account_id BIGINT PRIMARY KEY,
username VARCHAR(30),
password VARCHAR(30)
)
-- I have two more tables that stores their credentials in the above table:-
customer (
delivery_address VARCHAR(100),
account_id FOREIGN KEY,
..30 more customer columns
)
admin (
sp_admin_code INT,
account_id FOREIGN KEY,
..30 more admin columns
)
An admin cannot be a customer and use same username and password to login as a customer.
My Questions:-
1. Is this the proper way to implement this kind of relationship? If not, what is?
2. Do I need to add some constraint in the primary key of the account table to denote that it's single entry can either be referenced by customer table or by admin table at once?
3. Now we also know that, an entry on account can only exist if there is an entry for it on either customer table or admin table and vice versa. So do we need to or if we want do add some cascade/constraint, how do we do it or how should we do it or if we should do it at all? sorry im confused..
thank you!
Asked by Sidharth Bajpai
(103 rep)
Jun 15, 2024, 08:53 AM
Last activity: Jun 15, 2024, 12:56 PM
Last activity: Jun 15, 2024, 12:56 PM