i already saw a couple of forums with this particular question but they don't answer one factor i wish to know. i'll explain first my subject:

i've got a system where each log of multiple customers are joined towards the database (ex. User1 drenched in, User2 drenched in, User1 joined User management, User2 transformed password, etc). And so i could be expecting 100 to 200 records per user daily. At this time, i am doing the work in one table and also to notice, i have to remove using UserID.

my real question is, that is more effective? must i play one single table or produce a table per user?

i'm worried when i personally use just one table, the machine may have some difficulty blocking 1000's of records. i have read some benefits and drawbacks using multiple tables along with a single table especially concerning upgrading the table(s).

i should also know which saves more room? multiple table or single table?


As lengthy while you use indexes around the fields you are choosing from, you should not have speed problems (although indexes slow creates, also most are a poor factor). A table having a couple of 1000 records is certainly not to mySQL (or other database engine).

The overhead of making 1000's of tables is a lot worse -- say you need to create a switch to the fields inside your user table -- now you'd need to change 1000's of tables.

A table we regularly search against for any single record @ work has about 150,000 rows, and since the area we look for is indexed, the search time is within really small fractions of the second.

If you are choosing individuals records without needing the main key, create a catalog around the area you utilize to choose such as this:

CREATE INDEX my_column_name ON my_table(my_column_name);

Thats probably the most fundamental form. To explore it, check here

I'd opt for just one table. By having an index on userId, you need to have the ability to scale easily to countless rows with little problem.

A table per user is much more efficient, but it is generally poor design. The issue having a table per user could it be causes it to be hard to answer other sorts of questions like "who had been in user management yesterday?" or "the number of individuals have transformed their passwords?"

For space for storage used - I'd say a table per user would most likely use a bit more space, however the distinction between the 2 options ought to be quite small.

I'd opt for just 1 table. I certainly wouldn't are thinking about creating a brand new table whenever a user is added somewhere. The amount of records you mention for every day is really not really much data.

Also, create a catalog around the user column of the table to enhance query occasions.

Certainly just one table. Getting tables produced dynamically for organizations which are produced through the application doesn't scale. Also, you should make your queries with variable tables names, a thing that makes things hard to debug and keep. For those who have a catalog around the user id you utilize for blocking it isn't a large deal for any db to see countless lines.

Any database with credibility will handle just one table that contains everything user information having to break a sweat. Just one table is certainly the proper way to get it done.

Should you used multiple tables, you'd need to produce a new table whenever a new user registered. You'd need to produce a new statement object for every user you queried. It might be an entire mess.

I'd choose the only table too. You might like to choose multiple tables, when you wish to server multiple clients with various group of customers (multi tenancy). Otherwise if you opt for multiple tables, have a look only at that refactoring tool: http://www.liquibase.org/. That you can do schema modifications quickly.

I suppose, if you work with i.e. proper indexing, then your single table solution are capable of doing good enough (and also the maintenance is going to be less complicated).