It sounds like you are questioning if you need to split the master table up because there are so many columns. The short answer is no. I'll touch on a longer answer below.
In a simple way of looking at databases, it's about groups of datum and what those groups represent. Relational databases being relationship, or links, between groups of datum. So, when you think about what goes in a table, or group of tables, you are working out aspects of some "entity" by way of the data that represents this thing. As you already know, this is done with a master table and one or more child tables.
As an example, an employee. We have a name, dob, id number(s), address, phone number, hire date, and so on. Some of these things are 1 to 1 some are 1 to n. That's how we generally break out where things go from a design point of view. That gives us a master table and some number of child tables.
Most modern databases will not have a problem with a master table having what you think of as a large number of columns. Ten, twenty, a hundred, none if that matters to the database. So, from a logical design point of view there is no reason to break-up that master table. However, the real world and theory do not get along all the time. I must stress that these times are RARE, often a different way of looking at the data will allow you to express it in a different way and avoid the issue. If you find yourself having really huge numbers of columns, ask why they are there and try to look at them from another point of view.
Back to the employee example, phone number. There could be several numbers here. So do I make a column for home phone another for cell phone and yet another for work extension? I may, or I may make a child table for it. That table would have three fields, id, category, number. So, what looked like three columns in the master table is in fact moved to a child table. Same data, different way of looking at it.
I think this maybe close to home for your case, but I admit that I am guessing here. I could see a set of stats that represent a character looking like they are part of the master table but really, they could easily be express as a child table. You mentioned basic attributes and skills, both of which would raise a red flag for me if they are part of the master table. Again, I am making some assumptions based on my rpg experiences with both computer and pen&paper games. As your game evolves you may well find a reason to add a new skill, you may even add a new base attribute. You may change or throw out an existing skill or base attribute. Do you want your database to change too? Ideally, no your don't want to have to go back and change your database structure and then the code for the database layer and then system logic code layer and then the UI code layer. You want to changes like that to affect as little code as possible. So, maybe you have a skill table, in which each row is a skill. Likewise, a base attribute table with each attribute as a row. Note that I say row not column in these cases, just like with employee phone number.
I feel myself beginning to ramble, so now I stop.
So, that's a longer answer. To summarize it, no you don't need to break up a master table because it has many columns HOWEVER you do want to analyze those columns to make sure they can't be expressed another way, like child table rows.
Hope that helps.
Database Normalization
First Normal Form (1NF)
- Eliminate duplicate columns
- Create separate tables for each group of related data
You should create a table employee_access and each time a employee enters or quits the building record the date and time:
id, employee_id, date_time, operation
operation field should contain IN or OUT
You may ask why not two fields (in_time and out_time) ?
- speed
- simplicity
- versatility
If your company is like mine, people use to log several OUTs or INs and not exactly in pairs (sometimes they log IN when they're going OUT, but that's called Managers) ;)
Best Answer
Your arguments in favor table-per-user are wrong:
This kind of isolation only makes sense if you create a DB user for each user and let them directly run SQL queries on your database. You usually don't do that, but instead create an API for pre-defined queries. That API should do the isolation, not some separation of tables...
Not really. If you define the indexes properly, MariaDB can easily and efficiently perform user-specific queries. If you program correctly, table-per-user will actually result in slower queries because:
Why? because you don't have to write
WHERE user_id = 1234? But now you have to write insteadtransactions_1234, which might seem a bit easier, but that's only because we are writing SQL directly - in practice the user ID will be stored in a variable, so you'll have to write"... transactions_" + userId + "...instead ofWHERE user_id = @user_id(and set the user ID using prepared statement parameters).And even if you write SQL directly, one-table-for-all-users has other advantages that can help with analysis:
You can create views that'll help you with the analysis. The views can have a
user_idfield you can filter by, and MariaDB will be able to do that filter efficiently. With table-per-user you'd have to create a view for each user, which is far less maintainable - even if you never change the data structure you will want to modify the views or create new views.You can create SQL functions that receive the
user_idas an argument.Also, when you open the workbench to perform that analysis, do you really want to see 10,000 tables in the table list?