Mysql – Split database for security and privacy reasons

It is possible to use one-database-per-user or one-table-per-user models… But these will start to become unwieldy as the number of users in your application grows… MySQL (and, really, no RDBMS) handles hundreds of thousands of tables or databases very well.

Having an user_id (or similar) column on each table that contains user data is totally standard, and the "right" way to do what you're describing.

The process of data modeling is complex enough that you will need to go read some books about it to learn the details. The subject goes way beyond what can be covered here. However, to get you started, you can follow these (drastically oversimplified) steps:

Step 1: Make an Entity-Type for Each Tangible Thing

Start with making a logical data model. For each thing your system cares about (people, places, things, events) make an entity-type. For your system it might look like this:

Step 2: Identify Your Relationships

Look at your logical entity-types and determine how they relate to each other. Remember to focus on the relationships that matter to your system. Don't get bogged down looking at relationships that are possible or that are real, but which are not pertinent to your system.

Step 3: Transform Your Logical Model Into a Physical Model

Once you have a logical model that covers the objects and properties that are of interest to your system, you need to figure out how the logical model can be implemented using physical tables in your database.

This involves at least two very important steps:

• Resolving many-to-many relationships into intersection tables
• Normalizing your tables

In the case of your system, there are some many-to-many relationships that can be resolved. Normalization involves looking at table fields. Since we don't know what your fields are from your question, we can't really get into that topic here. Normalization is very important, however, and you should always make sure that you start from properly normalized tables and de-normalize only when you have very good, well considered, reasons to do so.

Your model with m:n relationships resolved would look like this:

Note that there isn't enough information in your question to be able to say for sure that this model is the start of what you need. I've had to make a number of assumptions about what your entities are and how they're related. This site isn't meant to be used for collaborative design in any case (see the FAQ) - but I've tried to give you a quick outline of the process so you have someplace to start. If you get stuck with a specific design issue along the way, then you can definitely come back to ask for advice about it here on dba.se.