I totally agree with Marnen's post above. The first approach is going to be better in terms of performance than storing it in another table.
The reasons are, basically: 1) Joins on the two tables during queries 2) Index size So let's say you have "users" and "addresses" and each address has a user_id FK. Cool. Totally valid approach, but again agreeing with Marnen, I can't see a reason why you'd need to store this in another table if each user is only going to have ONE data set for their address information. As Marnen points out, the extra time it would take to perform this query is negligible if your tables are properly indexed. However, those indexes could cause some additional overhead, both performance wise and financially, down the road. Each of those indexes will grow in size relative to the number of records in each table. So if you have only 5 entries, you won't notice a difference. 5 million though? Now we're talking! So, while you could certainly do it by including a second address table, it wouldn't necessarily provide a performance benefit, and if you have very large data sets - either now or in the future - could actually be detrimental to performance to some degree. Compensating for that may involve paying more money to your host for more disk space for your DB, possibly for upgraded memory limits as well, etc. But that really depends on the size/scope of your app. The other consideration that Marnen also alluded to is that you may not necessarily gain an advantage in design by storing address information in a secondary table. My general rule of thumb is: if it's going to have multiple "data sets" (or "records") per user record (in this case), it needs its own table. But if the system's design says "a user has one, and only one, address", then there's no reason to store addresses in a secondary table. They can simply be factored into the first table. I hope this helps you a bit :) Good luck! On Jan 2, 6:10 pm, Marnen Laibow-Koser <[email protected]> wrote: > David Zhu wrote in post #971909: > > > > > > > > > > > Hello, > > > 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? > > The first will be slightly better in terms of performance, because > you're not joining tables. But if your tables are properly indexed, the > join should have a negligible effect on performance. > > However, I don't see why you'd use the second approach. It's more > complex for no particular gain. > > > > > Thank you > > > -David Zhu > > Best, > -- > Marnen Laibow-Koserhttp://www.marnen.org > [email protected] > > Sent from my iPhone > > -- > Posted viahttp://www.ruby-forum.com/. -- 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.
