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!
Best Answer
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 atype
column. You could have two child tables to store data specific to each type of account if there would otherwise be too manynull
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:where
pro_attribute
is the name of an attribute that only applies to professional accounts.This could be simplified to:
but that's not clearer.
Then for your profiles, you need to apply the same pattern: One
profiles
table with aservice
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 oneprofile
for each service that exists. In this case I would create theprofiles
table with columnsaccount_id
(foreign key toaccounts
),service
(orservice_id
as a foreign key to aservices
table) and any attributes you need for that particular service for that particular account.