I have a database design issue that I'm not quite sure how to approach, nor if the benefits out weigh the costs. I'm hoping some P.SE members can give some feedback on my suggested design, as well as any similar experiences they may have came across.

As it goes, I am building an application that has large reporting demands. Speed is an important issue, as there will be peak usages throughout the year.

This application/database has a multiple-level, many-to-many relationship.


object a
object b
object c
object d

object b has relationship to object a
object c has relationship to object b, a
object d has relationship to object c, b, a

Theoretically, this could go on for unlimited levels, though logic dictates it could only go so far.

My idea here, to speed up reporting, would be to create a syndicate table that acts as a global many-to-many join table. In this table (with the given example), one might see:

| child_id | parent_id | type_id |
|    b     |     a     |    1    |
|    c     |     b     |    2    |
|    c     |     a     |    3    |
|    d     |     c     |    4    |
|    d     |     b     |    5    |
|    d     |     a     |    6    |

Where a, b, c and d would translate to their respective ID's in their respective tables. So, for ease of reporting all of a which exist on object d, one could query

SELECT * FROM `syndicates` ... JOINS TO child and parent tables ... WHERE parent_id=a and type_id=6;

rather than having a query with a join to each level up the chain.

The Problem

This table grows exponentially, and in a given year, could easily grow past 20,000 records for one client. Given multiple clients over multiple years, this table will VERY quickly explode to millions of records and beyond.

Now, the database will, in time, be partitioned across multiple servers, but I would like (as most would) to keep the number of servers as low as possible while still offering flexibility.

Also writes and updates would be exponentially longer (though possibly not noticeable to the end user) as there would be multiple inserts/updates/scans on this table to keep it in sync.

Am I going in the right direction here, or am I way off track. What would you do in a similar situation? This solution seems overly complex, but allows the greatest flexibility and fastest read-operations.

Sidenote 1 - This structure allows me to add new levels to the tree easily.

Sidenote 2 - The database querying for this database is done through an ORM framework.

  • 1
    Migrate this to SO – Job Dec 27 '10 at 3:29
  • @Job - I believe this is a subjective question, rather than a question requiring a specific solution. I'm asking how others might approach the problem, to give me an idea as to whether I'm on track or not. – Craige Dec 27 '10 at 3:45
  • better SO question... but in the example you gave above, is c a child of b and therefor a child of a or can c be a child of a without being a child of b? If it is multiple-parent child relationships there is a better way to relate them then the example. – Beth Whitezel Dec 27 '10 at 3:49
  • @BitOff I don't THINK I forsee any reason for skipping a relationship, as in your latter example. Please post an answer with your solution. – Craige Dec 28 '10 at 2:49
  • sorry, I re-read your example. I had missed that you said there was a many-to-many where each child could have multiple parents. I agree with andre below, that is a good solution and should keep your queries quick. – Beth Whitezel Dec 28 '10 at 4:40

Despite not being 100% sure about the ORM framework, it is probably a good idea to use MPTT to create your relationships between objects, your syndicate table.

This will allow unlimited levels of relationships and easy access to records, since you'll mostly be doing primary key lookups after getting the relationship information from this table.

The queries might not be very simple or easy to understand, please see this link for more information and examples.

Millions of records are not a problem, I've had experiences with tables around 10 million rows and with reasonable performance.

Despite the links pointing to the MySQL site, the concepts apply to any database (I just like that article).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.