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?
Asked
Active
Viewed 153 times
0
-
You could split it into two tables and expose data through a materialized view (http://www.fromdual.com/mysql-materialized-views). I don't know if there's an advantage to that or not (probably not), but it's an option. And it'll give you something new to learn/play with. – Roman May 28 '10 at 05:34
5 Answers
4
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
- 337,827
- 72
- 505
- 443
-
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) – Jun 13 '10 at 23:09
0
Use one table.
Because there is no reason to separate.
Your Common Sense
- 156,878
- 40
- 214
- 345
-
Maybe in this case there is no reason to share... but that is not always the case. To just blindly assume that is foolish. – Mitch Dempsey May 28 '10 at 05:30
0
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
- 15,267
- 10
- 70
- 113
-
-
@Col. Shrapnel: You could grant different access levels to a particular user to the tables independently. – Roman May 28 '10 at 05:39
-
-
@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 May 28 '10 at 17:11
-
Just curious, how much database users your typical applicaton use? – Your Common Sense May 28 '10 at 17:13
0
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
-1
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.
-
I didn't downvote you, but explicitly specifying the columns of your result set is usually considered a best practice. – Daniel Vassallo May 28 '10 at 05:43
-
-
no there will be two tables onw with all columns related to the login and one with all the columns of profile. – Salil May 28 '10 at 05: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 May 28 '10 at 06:01
-
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. – Your Common Sense May 28 '10 at 06:04