Data Model: Parent, Child, Grandchild with Child Being Optional

database-design

I posted the question in another forum and I was suggested to re-post it here which may be more appropriate. Thank you in advance.

My organization structure:
   Department — has many sections ( and employees)
      Section — has many employees
         Employee — most of the employees belong to Section directly.

However, there are two departments which have no medium Sections and employees of these Sections report to Department Director directly.

I do not know what can be done to better reflect the business structure using an Entity Relationship model.

I learned of a similar posting which is still different than what I need.
https://stackoverflow.com/questions/2530215/model-a-zero-or-one-to-many-relationship?rq=1

John

Best Answer

Ask yourself what is the difference between a Department and a Section? Are they just organizational objects that may contain either employees or other, smaller organizational objects? Do they have any other differences in terms of their relationships and attributes?

If the answer is no, then you might want to consider replacing both with a single organization table containing an unleveled hierarchy. This can be represented with an involuted (self-referencing) foreign key (i.e. part_of or reports_to etc.)

Of course, in a relational database, unleveled hierarchies can be awkward to work with. If you decide to go this route, you should do some research on hierarchical data management techniques, like visitation numbers or adjacency lists.

If you really need to keep Department and Section separate because they have markedly different definitions, then you will need to consider implementing a dummy or null Section for each Department to handle the situation where there is no analogue in reality. If you go this route, you could consider adding a flag or some other indicator to differentiate between real Sections and pro-forma Sections.

Related Solutions

Postgresql – Table for optional parent/child relationship

The solution you outlined is one valid option - assuming that an item can only belong to a single person at any given time.

In PostgreSQL you can enforce mutual exclusion between the two fk columns with a simple CHECK constraint:

either a Parent or Child must exists

... you can add a simple CHECK constraint:

CHECK (a IS NOT NULL OR b IS NOT NULL)

Would demand at least one NOT NULL column - but also allow that both parent_id and child_id exist. If you want to disallow that, too, make it:

CHECK (a IS NOT NULL AND b IS NULL OR b IS NOT NULL AND a IS NULL)

Database Design – Understanding a Notification System

Thanks for such thorough questions, and sorry for all of confusion - commenting 1 year after initial answer is hard and now 3 years after.. initial thoughts fade and confuse me too, but I fear to edit the hole thing because I'm not working on storing notifications on backend right now and not sure if I make good judgements without practical application

I think at the time of writing:

Yes and no, notificationID was a foreign key, notificationObjectID was not. You do need another FK field to tie tables together. I blame my mongo experience in not being so clear about it :(
Yes and no, notification_object.object is vague because you can have it just as a string, or something complex (JSON, or FK). In my case it was just a noun.

So it all depends on how your notifications look. For simple case, you just want to link entire notification to some URL, like friends page - thats why having object (entityType) as a string is useful - you tie URLs to it.

Notification "you have 3 friend requests added" can be stored differently.

If you want to show them one at a time - you'll have 3 notifications, 3 notification objects (friend_request) and 3 entries in notification_change that link to specific friend user.

If you want to show one notification - you'll have 1 notification, 1/more objects and 3/more actions. So in this complex case, like «you have 3 friend requests from user A, user B, user C» - you use notificationObjectIDs for each user and have several links in your notification text.

Should you use 1 or 3 friend_request objects? It depends on

What is the object and what is the action. Is «article liked/commented»? Or is it «like/comment added» and object hierarchy gets linked only during display? Here is facebook distinguishing «photo commenting» from «user mentioning» which semantically seem to be very close — you have same photo, same actor but different notifications that could have been merged together

enter image description here

Can you remove a notification or do you need a history? So if I send a friend request and then cancel it, or comment and then delete an article, like and then unlike something - should It show this history (as another action) to the end user as two different actions? Probably not. In addition its techincally more complex - you need to search if there is an existing notification_object, add new notification_change to it (if not - add a new object) so that later I would need to search through notification_change to remove it. Instead I would just add another notification_object to the same notification and remove it if its gone with actions getting cascade-deleted.

On the other hand, there could be cases, where it might be useful to have grouping of actions where nothing gets wiped from the history.

I think thats why at the time of writing, 1:n relation between left and middle tables were done - so that you could group notifications not only by actors on the same entity (middle-right tables) but by several object/entities too.

But sure, you can simplify entire case and optimize storage by reversing left-middle relationship to n:1, so that you have per-user notifications generated for one event.

So that it would it look more like this..

╔═════════════╗      ╔═══════════════════╗      ╔════════════════════╗
║notification ║      ║notification_object║      ║notification_change ║
╟─────────────╢      ╟───────────────────╢      ╟────────────────────╢
║ID           ║←—n:1—║ID                 ║—1:n—→║ID                  ║
║noteObjFK    ║      ║entityType         ║      ║noteObjFK           ║
║viewerUserID ║      ║entityID           ║      ║actionOnEntity      ║
╚═════════════╝      ╚═══════════════════╝      ║actorUserID         ║
                                                ╚════════════════════╝

Hope this helped

Best Answer

Related Solutions

Postgresql – Table for optional parent/child relationship

Database Design – Understanding a Notification System

Related Question