0

I am developing a social media type site. I have begun initial design and ran into potential issues. The site revolves around components, sub-components, and sub-sub-components. It has a hierarchy structure. I initially created a table for components, a table for sub-components, and a table for sub-sub-components. These tables are linked by ids. This would work fine, I believe, for most things. but...

I was wanting users to be able to "like" or "follow" certain components, sub-components, and sub-sub-components. I was thinking I would have to create a "like" table or "interaction" table. I had no single id to link. If a user would like a sub-sub-component I would have to list the component_id, the sub-component_id, and the sub-sub-component_id. This seems to be a pain.

So why not have 1 table of Elements or just Components. Sub-component1 and sub-component2 are the children of Component1. sub-sub-component1 is the child of sub-component2 etc.

In doing this I could create unique component_ids whether the component is a sub-component, or a sub-sub-component and I could apply those id's in my likes table.

Does this seem like a more efficient process? I have created the single table with parent_ids and such and have now run into problems with querying this table to produce data. How do I go about showing components, the child of the component (as sub-component), and the child of the sub component (as sub-sub-component)?

I hope this gives you an idea of what I am looking for. Please let me know if you need more information.

foxtangocharlie
  • 122
  • 8

1 Answers1

0

It depends on several things whether a separate table per level or a single table is better:

  1. Is the maximum number of levels known, fixed and fairly small?

    • No => you need a single table, otherwise you'd have too many different tables and could in theory build a deep enough structure that needed a table that didn't exist.

  2. How similar are the levels to each other?

    • If they behave the same, then you only need one table.

    • If the behaviour can vary, is that variation a function of only the level, or can it vary by something else? (Can components come in different flavours, regardless of level?)

      • If the variation in behaviour is just because of level, that's an argument in favour of different tables per level (although see the other points).
      • If the variation in behaviour is effectively random, I'd have a single table to show the structure, and then 1+ other tables that cover the differences in behaviour (that are linked to the structure table).

The component table would need a parent_id field on it, to let you find all the children of a given thing. You'd probably want an index on this too - I assume that you'd be reading the structure of components more often than it's changing, so the cost of the index is outweighed by its benefit.

An example of adding an index to a MySQL table is in this other SO question.

Another thing to consider is putting a level counter on each node. I.e. components are level 1, sub-components are level 2 etc. This would make some jobs easier, for instance if you wanted to present the levels with a different colour or something like that. But if you just want an undifferentiated tree of things, it's probably not worth the bother.

There are other questions on SO that go into the details of queries on hierarchical data in MySQL (there is more than one way to do it, with corresponding small differences in the table structure). Different database vendors give you different amounts of support for this kind of query, which makes it more or less easy. Oracle, for instance, has a very nice way to do hierarchies.

If you do go for a single table, the link between the different tables and the users' likes is an example of Polymorphic Association. (This is a link to an SO question, which in turn has a link to another - both are useful.)

Community
  • 1
  • 1
Bob Salmon
  • 411
  • 4
  • 10
  • This is outstanding. Thank you. to your first point. I can't for see the levels going past 5 but, conceivably, it is possible. Truthfully, it should be a capability with this application. – foxtangocharlie Apr 15 '16 at 14:10
  • So I am leaning towards one table. To your 2nd point. Many are very similar. example - a book shelf has books (title, description), chapter (title, description). but then the content in the chapters could be wildly different. A book may be a cook book and have chapter content that are recipes or the book could be straight fiction and just paragraphs. and of course those chapters could have sections etc. I think one table is the way to go. I also believe that indexing will be important because this table will get pretty huge. – foxtangocharlie Apr 15 '16 at 14:20
  • Can you direct me to any references involving how to insert the index? Also, how to query the data from the single table to show tree structure? Thanks again. Your input has been incredibly valuable. – foxtangocharlie Apr 15 '16 at 14:20
  • I've edited the answer with links on indexes and queries - hope that helps. – Bob Salmon Apr 16 '16 at 21:43
  • I've just a pair of SO questions on Polymorphic Associations in case you end up going with separate tables. http://stackoverflow.com/questions/7000283/what-is-the-best-way-to-implement-polymorphic-association-in-sql-server plus the one it links to. – Bob Salmon Apr 21 '16 at 06:51