Which is more efficient: Multiple MySQL tables or one large table?

Question

I store various user details in my MySQL database. Originally it was set up in various tables meaning data is linked with UserIds and outputting via sometimes complicated calls to display and manipulate the data as required. Setting up a new system, it almost makes sense to combine all of these tables into one big table of related content.

• Is this going to be a help or hindrance?
• Speed considerations in calling, updating or searching/manipulating?

Here's an example of some of my table structure(s):

• users - UserId, username, email, encrypted password, registration date, ip
• user_details - cookie data, name, address, contact details, affiliation, demographic data
• user_activity - contributions, last online, last viewing
• user_settings - profile display settings
• user_interests - advertising targetable variables
• user_levels - access rights
• user_stats - hits, tallies

---

Edit: I've upvoted all answers so far, they all have elements that essentially answer my question.

Most of the tables have a 1:1 relationship which was the main reason for denormalising them.

Are there going to be issues if the table spans across 100+ columns when a large portion of these cells are likely to remain empty?

Answer 1

Multiple tables help in the following ways / cases:

(a) if different people are going to be developing applications involving different tables, it makes sense to split them.

(b) If you want to give different kind of authorities to different people for different part of the data collection, it may be more convenient to split them. (Of course, you can look at defining views and giving authorization on them appropriately).

(c) For moving data to different places, especially during development, it may make sense to use tables resulting in smaller file sizes.

(d) Smaller foot print may give comfort while you develop applications on specific data collection of a single entity.

(e) It is a possibility: what you thought as a single value data may turn out to be really multiple values in future. e.g. credit limit is a single value field as of now. But tomorrow, you may decide to change the values as (date from, date to, credit value). Split tables might come handy now.

My vote would be for multiple tables - with data appropriately split.

Good luck.