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?

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

1 Answer 1

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 and acknowledge you have read our privacy policy.

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