CODEWITHSUNDEEP
mysql
Stylex
Stylex

Reputation: 73

MySQL Hierarchical Structure

Every single user has say, 3 of GROUP_A, 10 GROUP_B's per GROUP_A, and 20 GROUP_C's per GROUP_B. And each of the 20 GROUP_C's involve lots of inserts/deletes...And every piece of data is unique to one another for all GROUPs/users.

I'm not an expert, but I've done research but it's all theoretical at this point of course, and I don't have hands on experience with the implementation that's for sure. I think my options are something like 'adjacency lists' or 'nested sets'?

Any guidance into the right direction would be very much appreciated!

(I posted this on DBA stackexchange too but I'd really appreciate if I could get more opinions and help from the community!)

I know the trivial solution is just to have simple tables with foreign keys to the parent 'container' but I'm thinking about in the long term, in the event there's a million users or so.

Upvotes: 0

Views: 167

Answers (1)

MvG
MvG

Reputation: 60858

I know the trivial solution is just to have simple tables with foreign keys to the parent 'container' but I'm thinking about in the long term, in the event there's a million users or so.

I would go with just that approach. As long as the number of hierarchy levels remains fixed, the resulting scheme will likely scale well because it is so trivial. Fancy table structures and elaborate queries might work well enough for small data sets, but for large amounts of data, simple structures will work best.

Things would be a lot more difficult if the number of leverls might vary. If you want to be prepared for such cases, you could devise a different approach, but that would probably scale badly if the amount of data increases.

Upvotes: 2

Related Questions