[mysql] Implement specialization in ER diagram

Er diagram

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.

This question is related to mysql database erd

The answer is


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!]


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to erd

Implement specialization in ER diagram How to identify a strong vs weak relationship on ERD? How to generate an entity-relationship (ER) diagram using Oracle SQL Developer How to get ERD diagram for an existing database?