I want to implement this specialization relationship. Knowing that I will have different attributes for each account type. Here's my try:
accounts(account_id, email, username, password, ...) user_accounts(account_id, ..) admin_accounts(account_id, ..) messages(subject, body, from_account_id, to_account_id)
Where account_id in user_accounts and admin_acounts is the primary key and foreign key references account_id on accounts
And from_account_id, to_account_id in messages references account_id on accounts.
But what happens if I wanted to create a relationship with one of the account type only. For example I want to give permissions to admins to manage something, So I would do something like this:
permissions(admin_account_id, type, value, ..)
Where admin_account_id is a foreign key references account_id on admin_accounts
Is that possible? And what would happen If I tried to enter a permission for a user_account_id instead?
I hope my question is clear and excuse my English language.
So I assume your permissions table has a foreign key reference to admin_accounts table. If so because of referential integrity you will only be able to add permissions for account ids exsiting in the admin accounts table. Which also means that you wont be able to enter a user_account_id [assuming there are no duplicates!]
Source: Stackoverflow.com