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?
