> I have a user table that stores their username, email address, and > password. (along with salt, etc) > > However, now I want to have more complex information associated with > each user (address, etc). I'm no DBA expert, so in terms of > performance, which is better: > > - A user table that has all the fields in it (Username, Email, > password, country, state, zipcode, etc) > - A user table that only has username/password/email, that is > connected via a foreign key to another table that stores all the other > fields. > > Which is better? And why?
I tend to agree with what everyone else said... in addition to that... You really *REALLY* need to make sure that the business case is "one address per user". Sure, you're client will tell you that and you'll build it, and next week they'll realize they just assumed that "address" mean "home" and "work" and "vacation house", etc. And now you're gonna rebuild it. The longer it takes for that realization to happen the more you'll have to redo. Now, maybe that will never happen, and I wouldn't suggest splitting the tables on the 0.0001% chance it might happen, but it's worth taking some time now to consider that possibility. This sort of thing happens all time time... a user belongs to a group -> a user can belong to multiple groups a user has a phone number -> a user has lots of phone numbers etc... The other thing to check, which probably isn't an issue anymore, but it at some point in the past (years maybe, it's all fuzzy :) ... some databases would pad out the "record" to accommodate the maximum length for that record. Mostly this came down to issues around picking CHAR vs VARCHAR... So instead of: Philip|Hallstrom|[email protected] You'd get: Philip |Hallstrom |[email protected] So... take your example... if it's padded, and *most* users won't have an address at all, you may potentially use up a lot of space for nothing. The advantage of doing this is that the database knows exactly where the "last name" field begins in each record so it can find it faster than checking it's internals and seeing where the field starts for each record, and it's easy to update a record "in place" instead of appending it to the end and invalidating the existing row (think of it like fragmentation). I probably should erase everything I just said about CHAR/VARCHAR as I don't think it's worth considering, but you did ask and it's interesting (to me anyway :) -philip -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
