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, location (optional) and maybe other things. Should i have this in a separate mysql table? or should i share the table? and why?
Maybe in this case there is no reason to share... but that is not always the case. To just blindly assume that is foolish.
webdestroya
2010-05-28 05:30:08
Don't call it "share"! Don't you call your legs "shared" in one body? It is not shared. It is one body. And you need reasons only to separate, not to being in one body. "share"!
Col. Shrapnel
2010-05-28 05:39:27
+4
A:
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.
Daniel Vassallo
2010-05-28 05:33:15
This was mentioned to me by someone else. He said split up often use data and not often used data so more of the often use data can fit into the cache. Is this true in modern databases? Why should i split up access frequently from not so freq? (or written freq vs not freq in other cases) is a whole row loaded to the cache at a time? is more optimized when an entire row is loaded at one time? (i'll assume you dont know the answers if you reply mention what you do know)
acidzombie24
2010-06-13 23:09:24
A:
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?
Kangkan
2010-05-28 05:33:54
@Col. Shrapnel: You could grant different access levels to a particular user to the tables independently.
R0MANARMY
2010-05-28 05:39:08
@Col. Sharpnel: If you have two different tables, you can give different privileges to different user or different application/component accessing them. The authentication provider will have access to it, but only read-only for authenticating. But the registration module will insert new users into it. The access to the registration module might be restricted or on a secure HTTPS channel. But the profile info need to be available to users for editing almost all the time and thus might be vulnerable. Its like keeping your cash in a locker within your wardrobe that also has the clothes..
Kangkan
2010-05-28 17:11:24
Just curious, how much database users your typical applicaton use?
Col. Shrapnel
2010-05-28 17:13:37
A:
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:
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.
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
egrunin
2010-05-28 05:35:13
A:
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 culumns in the resultset. to overcome this i can use the "SELECT" only columns i want but that makes my query very ugly. similarly when i need all pofile data i have to use profile columns in select clause.
So always suggest to separate the tables wherever it is possible.
Salil
2010-05-28 05:35:25
I didn't downvote you, but explicitly specifying the columns of your result set is usually considered a best practice.
Daniel Vassallo
2010-05-28 05:43:44
no there will be two tables onw with all columns related to the login and one with all the columns of profile.
Salil
2010-05-28 05:59:59
@daniel:- i agree with you but if i have 5 more columns of addresses what will you do a table with 25 columns? or different table for adresses?
Salil
2010-05-28 06:01:47
but if you need only address from the profile, to use it to print letters, following your logic you should separate address from the rest.
Col. Shrapnel
2010-05-28 06:04:32