I’m developing a website. It must let diferent users to create an account, having his own panel, configuración and other data.
The prototype, just for only one user, requires several tables in database. My question is: ¿How do i develop my database in order to manage data from several users?
I have this idea: to create an “user_id” column for each table, so i can then filter every single user data. But it means a lot of rows by table.
¿I’m right, or there is another way to do it?
You’re not really clear about the technology you are using, but yeah, you’re going to need some kind of a database. Using a user id for the tabes makes sense if the data is associated with that specific user.
But I’m not sure what you mean by:
But it means a lot of rows by table.
I don’t understand how adding a column increases the number of rows.
Of course, some of this data could be stored on the client - like configuration could be stored in the browser storage - depending on what you mean by that and assuming there is no sensitive information in that.
Thanks for the answer.
Yes, i mean to data that belongs to a especific user.
I mean, adding a “user_id” column, it supose the table will have so much more items, because all users data is in there. At first sight, it appear to be a unefficient sistem, being a so much more long list where to looking for. Also is the only way i see. But, being unexperienced with large projects, i prefered to ask in here.
So, i undestand that is the correct way, right?
P/D: I use MySQL, but i think it is not the point.
I mean, I can’t see your DB structure so I can’t know, but if you need a piece of data, you need a piece of data - period. And if that data needs to be retrievable by the user id, then the user id has to be included in there.
Be careful of micro-optimization - it can be addicting. But in general, memory is cheap and it will probably be a long while before you run into trouble. It’s good to not make dumb choices, but you also shouldn’t be paralyzed with indecision.
Thank you so much, again.
Is good to know i wasn’t so wrong.
I will take your advice, and maybe t will be an good ocation to experience how fast o slow the data can be processed.
Just be willing to make mistakes. Just build things and learn as you go. Worry about progress, not perfection.
I think you mean foreign key of your user id to other tables? Then using joins to query the tables all together
Yes, @nomnomcookie , that’s what i mean.
I think your getting to thinking about database design, specifically database normalization.
Where you design your database structure to support the needs of your app. “Normalizing” a database is essentially setting up the structure of your database to prevent duplication of data. As duplicated data means updates are more annoying, and you take up more space.
So for a simple example we can start with 2 tables. 1 for
user's, and 1 for
panel-configurations. Where the user table represents the user itself, IE name, email, etc. And where the
panel-configurations is a table where the user can have multiple panel configurations. This is a 1 to many relationship, where a user can have multiple configurations.
The only real option here in a traditional relational database would be to save your
user_id on both the
user table and each row in the
yes this data is duplicated, but its highly optimized and is required to “relate” the data. Saving just this “key” for each table also allows most database’s to “index” this data so its much faster to lookup later. So if you want all configurations for a given
user_id the database already has the references to the rows. Thus making the entire process super fast, even if there are millions of rows in the same table.
So I’d look into database normalization, and keep up with the design.
Thanks for the detailed reply. I am familiar with relational databases, but the answer may serve others. However, I did not know that the keys made the search process faster. That was what worried me: searching within thousands or millions of records. Thanks again.