most optimised DB layout.

[eluser]TornUp[/eluser]
Hi all,

I was wondering if you was able to help me in regards to designing a DB layout for a social networking site.

I am currently working on the user tables and im not sure what the most optimised way of designing it is..

should i do it like this:

USERS

Code:

user_id
username
password
e-mail
etc....

ANALYTICS

Code:

id
user_id
logins(amount of time the user has logged in)
pm_sent(amount of time user has sent a PM)
pm_receaved
etc...

ACCESS

Code:

id
user_id
user_active(if this is set to 0, they can't log in)
send_pm(can the user send PM? 1 or 0)
etc...

And then do a DB Join via user_id for each table.

Or should I just put them all in one massive table?

What would the speed/load impact be between the 2 methods?

I hope somone can help, I want to make sure that i'm starting off on the right foot while designing this DB.

Many Thanks

Tom Kirby.

[eluser]Krzemo[/eluser]
They say that early optimisation is the root of all evil in programming
Generally I would use separate tables only when there is one-to-many relation.
JOINs is something to avoid when it comes to performance.

[eluser]WanWizard[/eluser]
Read up on database normalisation. If you follow the guidelines you'll see that all this info will end up in the same table, as they have a one-to-one relationship.

The only reason to deviate from these rules is performance, but this usually done in an application optimisation fase, unless you know it up front (p.e. MyISAM performance degrades when using large tables with variable length records. If you don't need the variable length fields most of the time, it makes sense from a performance perspective to split the table).

[eluser]TornUp[/eluser]
Many thanks guys! After thinking about it i'm not really sure what i was thinking when i decided to build it in split tables.

Thanks for all your help!

Tom.

[eluser]jedd[/eluser]
[quote author="TornUp" date="1275832830"]
I was wondering if you was able to help me in regards to designing a DB layout for a social networking site.
[/quote]

First thoughts -

o email should be in a separate table, if you ever want the ability to have more than one email address for a given user. Most sites do not, but sometimes this is useful

o user table PK should be 'id' not 'user_id'

o stick with singular or plural table names - I prefer and advocate singular - but just pick one and be consistent with it, otherwise it will confuse the shinola out of you very quickly

o do not de-normalise unless you have a spectacularly good reason to - f.e. counts for number of PM's sent - just generate that (count() from pm where pm.from = user.id) - that kind of thing. If you find that your DB can't cache this stuff adequately well for you, then you alter your user table to keep an aggregate column in there for this information - same applies for # of logins, pm's received, and so on.

o do not limit yourself to a boolean user_active flag - have a status flag in the user table, that can be set to inactive, banned, pending, locked, etc.

o things like send_pm can get confusing - I saw a good approach a while ago and have since adopted it that all booleans start with 'is_' - so 'is_pm_able' (for example). The idea is that 'send_pm' would be ambiguous later, but if you have something like 'is_...' you can spot immediately that it's a boolean. Similarly 'when_' for all date fields. Again .. modify to taste, but have a think about the benefits.