2

I have a database design issue, one that has rattled my brain.

What is the project?

I am designing a platform where different people register a group, each group is allowed to register their own members, it is important to keep these groups away from each other, as each are to their own, each group would have access to 65 or more tables with possibilities of 1000 of rows of data in each table, in their own database, each database is a copy of an empty master database.

Where i am stuck.

I do not know if this is wise cause i could end up with a 1,000 - 30,000 databases. I need to either create a database with a unique name (scenario above) or create a database with a unique environment variable, where each group has it's own environment variable.

Where things get messy.

Assigning each group to a unique database allows me to keep things simple, allows me to access all the data within the group relatively easy (vs searching through 1000 of groups data in one database). a simple but not too important, but still relevant eg. Each group can create things like their own invoices, so if all groups have unique databases then the invoice numbers would follow logically (users perspective), but if all groups are within the same database invoice numbers would not follow logically (users perspective).

The Question Then

Should I, or would it be wise, to create multiple database to keep my system efficient, bear in mind that there is no need to cross over from one group to another.

Improve this question
5
  • I don't really see why it would be problematic to have only one DB and assign the different data rows to these groups. I imagine that the group ID should be stored in relatively few tables and if you design your application carefully, the only difference between the queries in the two solutions will be a simple WHERE group_id = 1234.
    – dezso
    Jul 2, 2013 at 13:20
  • I like the idea of multiple databases. Keep in mind that other things will be much easier to do, snapshot backups for instance (which can lock the table) or indexes depending on the use cases of the groups (without slowing down INSERTs for other groups).
    – 0xCAFEBABE
    Jul 2, 2013 at 13:23
  • 1
    However keep in mind that your regular DR process will take longer and be a great deal more complicated. There is a certain amount of overhead in starting and ending a backup and having say 15,000 backups to do could make life interesting. I would go with @dezso and vote single database.
    – Kenneth Fisher
    Jul 2, 2013 at 13:30
  • Thank you for your responses. @dezso you are right, it's a simple change in code. FURTHER: "all roads lead to home", this project is basically complete and this question has only now come up, all that has to be done to launch is for me to open registration and i have taken the multiple database approach. What are the side effects of having gone this route?
    – Jacques
    Jul 2, 2013 at 15:26
  • also found a great answer: Building a web application with multiple database instances or just a single instance
    – Jacques
    Jul 2, 2013 at 19:37

1 Answer 1

Reset to default
2

I suggest:

  • A single database for all groups, but with
  • each table partitioned by group.

This should provide you with the convenience of a single database, combined with something like the performance you would expect from separate databases for each group.

Based on the comments on the related question on StackOverflow, you are using MySQL - you can find the official MySQL 5.5 Reference Manual section on partitioning here.

There is also a related StackOverflow question here, and a related blog entry here.

Improve this answer
4
  • I would go with partitioning only if the table sizes grow really big.
    – dezso
    Jul 2, 2013 at 16:41
  • can partitioning happen at a later stage? i don't really have an understanding of it and we are only starting up now?
    – Jacques
    Jul 2, 2013 at 19:36
  • @Jacques: Yes, partitioning can happen at a later stage.
    – user10933
    Jul 2, 2013 at 19:40
  • Thanks @MarkBannister i ended up changing my database structure and code to work with a single database, only took a couple of hours. I do feel more at ease with method. TO ALL the comments, Once again, Thank you guys and gal.
    – Jacques
    Jul 3, 2013 at 20:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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