CODEWITHSUNDEEP
databasedatabase-design
day0ops
day0ops

Reputation: 7502

Database design for a organization hierarchy

I have a simple use case where I've got the following data model for an Organization.

Org Details
===========
Org Id
Org Name
Org Type

What I would like to do is setup a nested Organization hierarchy so I can create a nested parent > child relationship between multiple Organizations. In other words, one parent organization will have multiple sub organizations. I thought of modelling it like so,

Org Hierarchy
=============
relationshipId
parentOrg   -------- Org Details(1)
subOrg      -------- Org Details(*)

-------- signifies the one-to-one and one-to-many mapping

Am I approaching this the right way ? or is there a better way ?

Upvotes: 0

Views: 1166

Answers (1)

reaanb
reaanb

Reputation: 10074

Recording a parent-child relationship in a separate table is a valid way of solving this. However, you don't need a surrogate relationshipId as primary key. Since each sub-organization can only have a single parent organization, subOrg is a suitable primary key. Alternatively, you can add a nullable parentOrg column to Org Details.

Upvotes: 1

Related Questions