- One account must have one sub-account
- One sub-account, depending on type, might have other sub-accounts (nested hierarchy).
Problems I am having
- Database doesn't guarantee consistency, as I can insert account without a sub-account.
- 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.
- Need a way to identify main sub-accounts in the sub-accounts table.
ParentSubAccountId IS NULLworks but not sure if it's the cleanest way to do it.
- 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.
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.
Is there a better approach to improve database design than the option I've considered?