Database design for storing individual users’ items

database-design

I am trying to make an application which users can have their own account
For the account, I have an single table and now I would like to add a function which
allows users to items in to their account

For example, user A want to add item A ,B ,C ,D to his account,
then on the database should have 4 rows of new data
Also there are no limit on the maximum number of item users can add.

My question will be how the item should I store on the database.
In a single table or a new table for each of the users?
Will the data retrieving time be very slow if there are lots of users with items?
And will many tables in a single database affect my works?

Best Answer

A general rule of thumb is that you will have a table for each type of thing that you are recording information about. Each instance of a thing will be a record in a table.

It would probably^* be a terrible idea to have one table per user. Have one table with USERS and one table with THINGS. In the THINGS table you will have a foreign key which is a column containing a user_id that points at the USERS record for the owner of the thing.

Relational databases are designed specifically to make this kind of structure easy and fast to work with. You shouldn't^* ever need to worry about how many users there are or how many things they have.

^* NOTE: Your requirements are very sparse. These are rules of thumb.

Related Solutions

Mysql – How to design a table for storing news items

A few things you will have to think about before starting:

How do you decide if a title is the same as a previous one? Say there is an article 'A god table for storing news items'. Your script discovers and stores it in your DB. Later the editors of that news site notice the typo and correct the title as 'A good table for storing news items'. What do you do in such a case? (The same applies to the content.)
If you want to do something with your data after storing, you will need a unique identifier in the table. Using this, you will be able to point to an item from other tables.
You may find useful to store the URL of the articles. However, from time to time these can change just like titles do.
Short news are typically found in many places on the internet. Both their title and contents are the same (like in the case of short news from news agencies) - are these the same or not? If you store the URL, then these are different. If not, these are the same. What to do with these?
If you decide that you will store these from each site you process, than you will find useful to identify your source sites. (Be careful, the URL will look like 'http://dba.stackexchange.com/questions/23568/how-to-design-a-good-table-for-real-time-news-extraction' and the site will be 'dba.stackexchange.com'. Here the latter forms a part of the former, but this is not necessarily true for other sites.) In this case, you will have to create another table where you list all the source sites. This table will be referenced from your other table.

So far we have the following database structure:

Table "news_item"

+--------------+------------+--------------+---------------------+-----+-----------+
| news_item_id |   title    |   content    |     time_created    | URL | source_id |
+--------------+------------+--------------+---------------------+-----+-----------+
| 1            | First item | some content | 2012-09-03 22:53:14 | ... | 1         |
.                                                                                  .
.                                                                                  .
.                                                                                  .
+--------------+------------+--------------+---------------------+-----+-----------+

Table "source"

+-----------+-----------------------+
| source_id | name                  |
+-----------+-----------------------+
| 1         | dba.stackexchange.com |
.                                   .
.                                   .
.                                   .
+-----------+-----------------------+

You can write your SQL script only after answering at least the above questions.

Mysql – Need advice for table design for multi-user access

I'd try creating a group and user_group table. Then change map_user_id to map_group_id and your group all have access to manage that item.

Eg.

+------------------+------------------+
| primary_group_id | group_name       |
+------------------+------------------+
|             1    | Edit Stereos     |
+------------------+------------------+

+---------------+---------------+---------------+
| user_group_id | user_id       | group_id      |
+---------------+---------------+---------------+
|          1    | 1             | 1             |
|          2    | 4             | 1             |
+---------------+---------------+---------------+

+--------------+-------------+----------------+------------+-----------+
| map_group_id | map_item_id | unique_item_id | item_owner | privilege |
+--------------+-------------+----------------+------------+-----------+
|            1 |       100   |              1 |          1 |      NULL |

Best Answer

Related Solutions

Mysql – How to design a table for storing news items

Mysql – Need advice for table design for multi-user access

Related Question