1

I have seen an article in Dzone regarding Post and Post Details (two different entities) and the relations between them. There the post and its details are in different tables. But as I see it, Post Detail is an embeddable part because it cannot be used without the "parent" Post. So what is the logic to separate it in another table?

Please give me a more clear explanation when to use which one?

APC
  • 137,061
  • 19
  • 153
  • 266
rachel
  • 3
  • 1
  • 1
    Can you give more context here? Indeed, there may be no need for two tables, but that decision was likely made for a valid reason, which showing some data might explain. – Tim Biegeleisen May 17 '17 at 06:53
  • Please add the link of the post – Dwhitz May 17 '17 at 07:10
  • Storing structured data together with its parent row can be a good concept in cases, where this data is *one bit of information*. At [this link](http://stackoverflow.com/a/43495014/5089204) I answer the question *When should I save a JSON **as is**?* This is not eactly the same, but it is very related. Might be interesting... – Shnugo May 17 '17 at 07:28

1 Answers1

2

Embeddable classes represent the state of their parent classes. So to take your example, a StackOverflow POST has an ID which is invariant and used in an unbreakable URL for sharing e.g. http://stackoverflow.com/q/44017535/146325. There are a series of other attributes (state, votes, etc) which are scalar properties. When the post gets edited we have various versions of the text (which are kept and visible to people with sufficient rep). Those are your POST DETAILS.

"what is the logic to separate it in another table?"

Because keeping different things in separate tables is what relational databases do. The standard way of representing this data model is a parent table POST and child table POST_DETAIL with a defined relationship enforced through a foreign key.

Embeddable is a concept from object-oriented programming. Oracle does support object-relational constructs in the database. So it would be possible to define a POST_DETAIL Type and create a POST Table which has a column declared as a nested table of that Type. However, that would be a bad design for two reasons:

  1. The SQL for working with nested tables is clunky. For instance, to get the POST and the latest version of its text would require unnesting the collection of details every time we need to display it. Computationally not much different from joining to a child table and filtering on latest version flag, but harder to optimise.
  2. Children can have children themselves. In the case of Posts, Tags are details because they can vary due to editing. But if you embed TAG in POST_DETAIL embedded in POST how easy would it be to find all the Posts with an [oracle] tag?

This is the difference between Object-Oriented design and relational design.

OO is strongly hierarchical: everything is belongs to something and the way to get the detail is through the parent. This approach works well when dealing with single instances of things, and so is appropriate for UI design.

Relational prioritises commonality: everything of the same type is grouped together with links to other things. This approach is suited for dealing with sets of things, and so is appropriate for data management tasks (do you want to find all the employees who work in BERLIN or whose job is ENGINEER or who are managed by ELLIOTT?)

"give me a more clear explanation when to use which one"

Always store the data relationally in separate tables. Build APIs using OO patterns when it makes sense to do so.

APC
  • 137,061
  • 19
  • 153
  • 266
  • Just one pedantic niggle. 'Relational' does not actually prioritise relationships. FK relationships are a clunky work-round. The 'relations' in RDBMS refers to the fact that all the rows in a particular table are related in that they are the same type of thing not that they relate to other rows in other tables. Other than that - good answer. – BriteSponge May 17 '17 at 08:39
  • @BriteSponge - thanks for the feedback. I was rushing to finish this and didn't properly read it through. – APC May 17 '17 at 09:23