0

I have a system where I need to have a one-to-many relationship between Boxes and Sections. However, Sections can either contain "Content", or another Box - this would be recursive. So, I have two tables (this is oversimplified, but it shows the basic idea) :

Box - UUID (int, PK) - Name - Name of this Box - ParentSection (int,FK) - The UUID of the Section that this is in (null would be a top-level element)

Section - UUID (int, PK) - BoxUUID (FK) - Which box this section belongs to - Name - Name of this section - Type - 'Box' or 'Content' (if 'Box', the Content field will be null) - Content - Content for this section

So, Boxes contains many Sections (through the BoxUUID), and sections contain Content (E.g. a string of data). A section could also be a "Box" type, in which case, the Content would be null, and the Boxes would then use the ParentSection column to define the hierarchy.

There are a couple of issues with this, and any advice would be great. :-

  • It makes retrieving the 'children' boxes of a section much more difficult.
  • It just feels wrong. We are using an ORM (eloquant, if that helps) and defining these relationships is not trivial.

Does this seem like the correct structure to use, and how could it be changed to be following best-practices?

verenion
  • 383
  • 1
  • 13
  • Yeah that's the problem of hierarchical relational databases and there really no best practice for it. You can google for structure types, some are good for easy data retrieval, some are good for data manipulation. It depends on what your app will do most of the time, either storing or retrieving. – TheDrot Jun 15 '16 at 22:54
  • 1
    I had a project with a similar case we were waffling between MySQL and MongoDB, but it was cancelled before a solution was architected. If your hierarchical data contains relational data, which is seems like it does, then general consensus was leading up to back-ending with MySQL and using a front-end cache in MongoDB. You build the Mongo cache with brutal recursive MySQL queries, but retrieval is quick. – Jeff Puckett Jun 15 '16 at 23:12
  • 1
    3 helpful bookmarks: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ http://www.sarahmei.com/blog/2013/11/11/why-you-should-never-use-mongodb/ – Jeff Puckett Jun 15 '16 at 23:13

0 Answers0