1

I have following database schema (simplified). Database schema

Requirements

  • One account must have one sub-account
  • One sub-account, depending on type, might have other sub-accounts (nested hierarchy).

Problems I am having

  1. Database doesn't guarantee consistency, as I can insert account without a sub-account.
  2. Joining account and the main sub-account might result in multiple rows if sub-account has other sub-accounts when only account and the main sub-account data is needed.
  3. Need a way to identify main sub-accounts in the sub-accounts table. ParentSubAccountId IS NULL works but not sure if it's the cleanest way to do it.

Notes

  • The main sub-account contains exactly the same fields, as other sub-accounts, hence was originally placed in the same table.
  • Sub-account children could be retrieved as a flat list with parent sub-account IDs, and hierarchical structure will be restored at software layer.

Options considered

Database schema 2

This solves #1 and #2 problems, #3 stays the same, however I need to perform recursive select to retrieve all sub-accounts for an account.

Questions

Is there a better approach to improve database design than the option I've considered?

  • If you must deal with hierarchy, why don't use only one table? – McNets Feb 5 '18 at 22:52
  • I need to have two distinct and unique IDs exposed to identify account and each sub-account entities. – Ignas Feb 6 '18 at 7:11
  • 1
    What DBMS is this for? – ypercubeᵀᴹ Feb 25 '18 at 21:14
  • 1
    Microsoft SQL Server. – Ignas Feb 26 '18 at 8:05
1

The option I considered initially was a good start, just needed further improving. The schema of the solution is below.

enter image description here

The solution consists of two parts actually:

  • Add unique constraint on SubAccountId
  • Introduce table for SubAccount children hierarchy mapping, both columns have foreign key to Id of the SubAccount table.

Children nested hierarchy is restored at software layer, the adjacency list hierarchy was not really needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.