How many tables should i use

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

How many tables should i use

Post by social_experiment »

Im working on an application that will require a bit of user information. My thinking about this is as follows: Have 3 seperate tables linked by a foreign key (user_id). What is the better option here, using a single table containing all information about a user or splitting the larger table into 3 smaller tables. The site will probably have alot of tracffic so i'm thinking this (3 tables) will be a better option if lots of people access the database simultaneously.
--------
user_tbl
--------
id
username
password
salt
user_id
session_id

--------
user_info_tbl
--------
id
user_id
real_name
surname
likes
dislikes
gender

---------
user_activity_tbl
---------
id
data
activity
user_id
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How many tables should i use

Post by Benjamin »

I don't see any reason to split those into 3, unless there's a couple fields that are large and aren't used often. In that case just those two could go into another table just to reduce any potential disk io.

Splitting the tables like that would likely HURT performance and increase production expenses. I've seen lots of large sites (>50M page views per hour) with many more fields than that in user tables.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: How many tables should i use

Post by social_experiment »

Thank you for the feedback; At the moment i'm not sure about the size of specific fields but in terms of usage of each field, the type of site would probably use all the fields most of the time. The given amount of fields are only an estimation and will definitely increase. Any idea what amount of fields would be 'too much' for a single table?

You mention large fields, do those include fields of type TEXT?
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: How many tables should i use

Post by Benjamin »

Well by large I mean BLOB and the like. Text would be an example. The key to remember is that MySQL uses indexes to find records. Each query can only use 1 index per table. This means you cannot both query by (WHERE location = 'foo') and sort (ORDER BY age DESC) using different indexes. In other words, you might have location and age indexed individually, but MySQL will only use one in a single query. As long as your queries are able to hit an index in the WHERE clause you should be good to go. As the site grows you may need to tweak memory allocations in the my.cnf file.

There will be times when a table scan may be necessary. It is this case that separating the very large fields may help. That's something I have never tested, I'm only going with my intuition on that.
Post Reply