How do I allow multiple users to use my web app & database?

Question

I am a novice in SQL organization! I essentially have an inventory app written in php with an sql database. If I want to allow multiple users to create inventories independent of one another using my web app, would I just use foreign keys linked to a user table or should I be creating new tables for each user, or another option that I have not considered?

thank you!

Answer 1

I have said before that it's a code smell if you hear the word "per" in the context of of database design.

I'm going to create a table per user.

That could result in a lot of tables. How many users will you have? Hundreds? Thousands? Millions? Can your database handle millions of tables? How can you test this? Does that mean your application must create new tables on the fly as new users register?

In your application code, would you create a separate PHP class for each user? Probably not -- you'd create one class that can be reused for many users. One of the class data members would distinguish the user, so each object instance of that class is for one user. But it's the same class.

Likewise, it's almost always better to create one table, and make sure the table includes a column to identify the user. It's simpler to add new users by INSERTing new rows into the table, instead of requiring new tables to be created.

There are exceptions to every rule, of course. You might have so many users that you must split the database over multiple database servers. Or you might have a very strict privacy requirement and separating the data into multiple tables is a good way to enforce it. But if you're just starting out with a small project, those exception cases probably don't affect you yet.