I have a the following use case where I want to model accounting accounts.

The Accounts can be

• External if they are assigned to a client.
• Internal if they are not assigned to a client.

There must exist a many to many relation bewteen external and internal accounts, where one external account can be mapped to one internal account and one internal account can be mapped to many external accounts.

The accounts whether they are external or internal should have the same columns, except for the external that should have a clientId foreign key.

Should I create?

Option A: 2 tables for accounts (ExternalAccount, InternalAccount) and 1 table for the mapping (AccountMapping)

or

Option B: 1 table for accounts (Account) and 1 table for the mapping (AccountMapping)? external accounts would have clientId defined, and internal would have clientId=NULL

With option A, it's easier to restrict the mapping in the AccountMapping table, any of the foreign keys refers to a different table and entity.

With option B, how could I restrict in the AccountMapping that 2 external account cannot be linked together?

CodePudding user response：

I think I would use only one table for this and use boolean etc to mark if it's external/internal. External then has values for foreignKeys, internal has that column value as null/empty.

ID | Name | External | ForeignKeys
1  | Internal Account  | 0 | NULL
2  | External Acccount | 1 | 1

Restrictions in this case must be done in applications business logic or maybe with an SQL constraint or trigger.

CodePudding user response：

Maybe something like this...

Table CUSTOMERS

Table ACCOUNTS

Table ACCOUNTS_LINKS

Regards...