Multiple table or one single table?

sql split table into multiple tables
why is it better to have multiple separate tables?
when designing a database why is it not preferable to store all data in one single table?
mysql split table into multiple tables
why use multiple tables in a database
database design when to split tables
single table design
sql related tables

I already saw a few forums with this question but they do not answer one thing I want to know. I'll explain first my topic:

I have a system where each log of multiple users are entered to the database (ex. User1 logged in, User2 logged in, User1 entered User management, User2 changed password, etc). So I would be expecting 100 to 200 entries per user per day. Right now, I'm doing it in a single table and to view it, I just have to filter out using UserID.

My question is, which is more efficient? Should I use one single table or create a table per user?

I am worried that if I use a single table, the system might have some difficulty filtering thousands of entries. I've read some pros and cons using multiple tables and a single table especially concerning updating the table(s).

I also want to know which one saves more space? multiple table or single table?

As long as you use indexes on the fields you're selecting from, you shouldn't have any speed problems (although indexes slow writes, so too many are a bad thing). A table with a few thousand entries is nothing to mySQL (or any other database engine).

The overhead of creating thousands of tables is much worse -- say you want to make a change to the fields in your user table -- now you'd have to change thousands of tables.

A table we regularly search against for a single record @ work has about 150,000 rows, and because the field we search for is indexed, the search time is in very small fractions of a second.

If you're selecting those records without using the primary key, create an index on the field you use to select like this:

CREATE INDEX my_column_name ON my_table(my_column_name);

Thats the most basic form. To learn more about it, check here

Splitting data into related tables (article), Basically a single table is good when data is one-to-one. When you have thousands of rows and columns of data, where the data is one-to-many, multiple tables  Free Delivery on Eligible Orders. Read Customer Reviews & Find Best Sellers

I would go with a single table. With an index on userId, you should be able to scale easily to millions of rows with little issue.

A table per user might be more efficient, but it's generally poor design. The problem with a table per user is it makes it difficult to answer other kinds of questions like "who was in user management yesterday?" or "how many people have changed their passwords?"

As for storage space used - I would say a table per user would probably use a little more space, but the difference between the two options should be quite small.

The What, Why, and When of Single-Table Design with DynamoDB, And one of the big benefits of joins is the ability to get multiple, heterogenous items from your database in a single request. In our example above,  Having many tables just to store post data only leads to bigger queries and multiple joins without saying that introducing new post types can be problematic whereas using a single table I can manage several post types without the need to alter the structure.

I would go with just 1 table. I certainly wouldn't want to create a new table every time a user is added to the system. The number of entries you mention for each day really is really not that much data.

Also, create an index on the user column of your table to improve query times.

single table w/ extra columns vs multiple tables which duplicate , Ultimately, it comes down to use and architecture. Architecture. Does the system handle "any sport"? Is the idea that you put on your  I'm finding that my tables for each object type are all nearly identical because each object type shares ~ 90% of the same property types. For example, the tables may have nearly the exact same column names (& data types), but one table may have 1-2 extra columns to refer to properties specific to that object type.

Definitely a single table. Having tables created dynamically for entities that are created by the application does not scale. Also, you would need to create your queries with variable tables names, something which makes things difficult to debug and maintain. If you have an index on the user id you use for filtering it's not a big deal for a db to work through millions of lines.

When To Use Single Table Inheritance vs Multiple Table Inheritance, Rails supports STI right out of the box simply by inheriting subclasses from an ActiveRecord parent class which has a type column in its table. For  If you have one table with all items in it rather than eight separate tables, you reduce the number of alarms and metrics to watch. Second, having a single table can save you money as compared to having multiple tables. With each table you have, you need to provision read and write capacity units.

Any database worth its salt will handle a single table containing all that user information without breaking a sweat. A single table is definitely the right way to do it.

If you used multiple tables, you'd need to create a new table every time a new user registered. You'd need to create a new statement object for each user you queried. It would be a complete mess.

Creating multiple tables and table relationships, For the purposes of this book we're going to try and keep things simple and draw a direct correlation between an entity and a single table of data; in a real  Any database worth its salt will handle a single table containing all that user information without breaking a sweat. A single table is definitely the right way to do it. If you used multiple tables, you'd need to create a new table every time a new user registered. You'd need to create a new statement object for each user you queried.

Table Joins, a must, Joins allow you to link data from two or more tables together into a single query result--from one single SELECT statement. A "Join" can be recognized in a SQL  Hi Nick - a data soruce is a connection to a single database, a data set is only one table. To use multiple tables, you either need to join then the SQL query (to create a single table) or use multiple datasets.

SQLite SELECT - Querying Data From a Single Table, use the simplest form of SQLite SELECT statement to query data from a single table. You can use multiple expressions in the SELECT statement as follows:. each table might have a few properties that the other one doesn't, and several that are shared. it goes on for say 5-10 tables across 4 or 5 sports. I'm still not sure if this is entirely a bad thing - the alternative, having a single set of tables which had properties on it that not all sports would use, might have on its own been unwieldy as

Using SELECT to query data from a single table and multiple tables , SUMMARY: This article covers the SELECT statement in PostgreSQL and how to use it to query data from single and multiple tables: 1. Merging data from multiple tables into a single column You can merge data from two or more tables into a single column on a report by using the keyword UNION.

Comments
  • We have tables with over 15 million records and still the search for one person is in milliseconds.
  • I've never had the experience of dealing with tables that size - nice to know the search time stays that low.