Database design for multiple account types management system

Question

I am trying to create multiple account types management system.

At first time, I have to types normal type, and professional type (each type has own table, so I have two tables at this time), and new tables are coming.

Each account type has some profiles. Each profile has own table, example: table profile_from_service_A, table profile_from_service_B, ... (new profile types are coming).

When new record is inserted to normal (or professional) table, I call to service A, service B, .. to create profiles, and insert profile result to corresponding table. There is not any relation between these service about business domain.

Most used query is used for reporting , example result: (account_id, account_type, profile_A_id, profile_A_propery_1, profile_A_propery_2, profile_B_id, profile_B_name, profile_E_id,...)

I intend create two mapping tables. For profile_from_service_A, mapping table is service_A_account_mapping with columns: (id_service_A, account_type, accounnt_id), account_type is normal or professional at this time.

This structure is also used for service_B_account_mapping, but I use id_service_B instead of id_service_A

If there is new profile, I have to create new mapping table.

Another solution: add profile_A_id, profile_B_id to normal, professional tables. Don't use mapping table anymore.

So which solution is better?

Thank you!

Answer 1

You are going down the path of table explosion with resulting complicated queries.

In a relational database, you want fewer tables but more columns, fewer columns but more rows.

Applying this mantra: your two types of accounts should be one accounts table with a type column. You could have two child tables to store data specific to each type of account if there would otherwise be too many null columns. But if there are only one or two such attributes I would personally add them as nullable columns and add a constraint something like:

 alter table accounts
   add check (type = 'normal' and pro_attribute is null or type = 'professional' and pro_attribute is not null);

where pro_attribute is the name of an attribute that only applies to professional accounts.

This could be simplified to:

alter table accounts
  add check ((type = 'normal') = (pro_attribute is null));

but that's not clearer.

Then for your profiles, you need to apply the same pattern: One profiles table with a service column. If individual services have different attributes you can store them in their own child tables.

As far as the relationship to services: it sounds like each account has one profile for each service that exists. In this case I would create the profiles table with columns account_id (foreign key to accounts), service (or service_id as a foreign key to a services table) and any attributes you need for that particular service for that particular account.