0

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!

2
  • 1
    "cannot ... use same username and password" So you need username to be UNIQUE as well as account_id PRIMARY KEY?
    – AntC
    Commented Jun 16 at 0:38
  • @AntC ah yes, I need that, thanks! Commented Jun 16 at 13:59

1 Answer 1

0

There are no constraints to do that in any RDBMS I know...

Traditional way is just go outside of the database. Have a field admin_or_customer in the account table and let client application to read one table or another according to that field. It is possible in that scenario to have records in both competing tables with same account_id, but if your client application always go from account first - the "wrong" row becomes a hidden orphan and not really play any role. You can also do regular cleanup of such orphans.

It is possible to add insert, update triggers on admin and customer tables, so they would check the flag in account and prevent creation of new row or modification of account_id field if it clashes with the flag or if row with such account_id already exist in another table.

Another trigger can be created on account, which would automatically create/delete/move records between admin and customer according to the flag.

1
  • 1
    I agree no SQL RDBMS implements this, except via triggers validation (yeuch). The ERPs I've worked with turn this into a 'feature': a customer might also be an employee might also be a supplier might also be an agent ...
    – AntC
    Commented Jun 16 at 0:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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