Ruby on Rails

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 <li...@ruby-forum.com> 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
> mar...@marnen.org
>
> 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 rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.