The way you think best. We have no way of knowing what is best without knowing every detail, and especially how it might happen in the future. The technology you use or the amount of users, in general, matters little.
I don’t even know if the description is correct, because it is very difficult for someone to tell how many simultaneous users they will have without running the system. Almost always has much less than the person imagines, that is, real simultaneous usually has 2, 3 or 4, but if there are many, then you can’t be very precise, you can talk in 50, in 100, round numbers. Simultaneity is not what you think it is. That’s why people think they need a much more complex architecture than they really need.
What you’re talking about has nothing to do with multi-tenancy, the main reason I answered. This concept is the opposite of what you are describing, is when the system must adapt to run for several different customers.
The issue of backup has nothing to do with it. It’s another problem and it’s not because of the model itself but because of the way it’s being done and how it’s been set up.
Being together or not, has nothing to do with the performance in any engine Mysql. Other decisions are what make the performance good or bad. It can influence a minimum, but nothing that makes a real difference.
Of course, each model adopted will bring advantages and disadvantages and has to balance each one. One of them will make you have more difficulty with some operations and another model will have with other operations, there is nothing free of disadvantages.
I prefer whenever possible in a relational database to put everything on the same basis, see little or no advantage in separating. You have to see, but I obviously disagree with them. Every time you have a relationship, you need a transaction between tables, if you’re not on the same base you become a martyrdom, and if you don’t have something like that on your set today, one day you might. It is almost always difficult to justify the motivation to separate, because it is so much work in more complex applications that it is not worth the effort, and in the simple either, until it is no longer simple.
Imagine having to think about separating some things and putting others together. The case of the users of your example. Whether it should integrate with everything or does it separately and turns to integrate with everything (this case may be simple, not all are) or then puts together. But how to put together if you have separate parts? Replicating? It looks horrible, close to unfeasible. And if what worked well apart one day changes and needs to be together?
This site is one of the 50 most accessed in the world and has everything together, runs extremely simple and with absurdly good performance. Unless you work on Facebook, Netflix, these companies, I don’t see how being separate is a good idea. But the current fashion of the market is to separate (I have talk showing how this is more problematic than people think).
But I often say that if you don’t know what you’re doing, if you don’t understand the fundamentals, if you don’t master every detail of the technology you’re using, there’s no point in making an architectural decision, all the architectures will go wrong, the problem isn’t at this point.
So I have this thought, all on the same basis organizing tables, procs etc by nomenclature. Now, this fashion business makes me suspicious. You said you have lectures on this, you would have some content to indicate, I need to base my decision.
– cpll
Not much, it’s my experience that I use in my talk.
– Maniero
Maniero, that’s what I really needed. A few examples, success stories.
– cpll