To share a table or not share?

Right now on my (beta) site i have a table called user data which stores name, hash(password), ipaddr, sessionkey, email and message number. Now i would like the user to have a profile description, signature,开发者_Go百科 location (optional) and maybe other things. Should i have this in a separate mysql table? or should i share the table? and why?

This is called vertical partitioning, and in general it will not make much difference whichever option you go for.

One vertical partitioning method is to split fields that are updated frequently from fields that are updated rarely. Imagine a Users table in Stack Overflow's database, having User_ID, DisplayName, Location, etc... and then UpVotes, DownVotes, LastSeen, NumberOfQuestionsAsked, etc. The first set of fields changes rarely (only on profile edits), while the latter set change frequently (on normal activity).

Another splitting method could be between data that is accessed frequently, from data that is accessed rarely.

This kind of partitioning, in some particular situations, can yield better performance.

Use one table.
Because there is no reason to separate.

IMHO, the authentication information and the profile info should be separate. This will secure your data. Of course, if the trust level is high, you can go for a merged table. But the information in the table might grow over time and at the end you will have to separate them out. So why to mess now?

If the tables you are thinking of will have a one-to-one relationship, there's usually no reason to do it.

Exceptions, both of which only apply when there are many, many columns:

1. if there are a few columns that will always be filled and the rest almost never -- in this case, there can be some benefit from omitting empty rows in the second table, and only fetching them when needed.

2. if there are a few columns that will constantly be retrieved/updated and the rest almost never.

But again, this is not an optimization you should do at the beginning. If you have your query code reasonably isolated, it's not hard to do this later on.

There are also some relevant comments on this elsewhere on StackOverflow

I think it depends on the your application nature or you can say requirement.

I prefer it should be in the different tables.

Consider example where I need users email, message number and store's name. So when I find all the the user from the table and all my profile related data in the same table, I get all the unwanted columns in the result set. To overcome this, I can use the SELECT only columns I want but that makes my query very ugly.

Similarly when I need all profile data I have to use profile columns in select clause.

So always suggest to separate the tables wherever it is possible.