NYCPHP Meetup

[tedd]

At 4:54 PM -0500 3/5/11, David Krings wrote:
>On 3/4/2011 3:07 PM, D. J. Waletzky wrote:
>>The problem with eliminating "redundant" info in a user table is that it
>>may not scale terribly well. I always take care to give any user table
>>an auto_incremented row number/uid, because the user's handle and
>>e-mail, though unique, may change. Without an independent index
>>changing either of these columns in the user table will cause all kinds
>>of headaches in other linked tables. With a row number you can save
>>space and CPU cycles by using numeric IDs for foreign keys.
>
>From my experience it isn't worth the effort to remove "redundant" 
>info, what I assume is meant as normalizing tables. I don't think 
>the numeric key is the culprit in scaling issues, but the many joins 
>one has to make across tables. Also, when something goes wrong and 
>you need to dig through the tables it is a pain in the you know 
>where to hangle from one table to the other to gather up all the 
>information that you need.
>Stick everything into one table and if it makes sense duplicate 
>partial data in a different table to minimize joins. Not only are 
>the queries much simpler then and get executed faster, figuring out 
>what goes on is also much easier. As a side effect you can find data 
>inaccuracies easier and fix them as well. The downside is drive 
>space usage, which will go up. But the times where a 120MB hard 
>drive cost 800$ are long gone - and so is the prime reason for 
>normalization.
>
>David


You won't have any "redundant" info if you use email as unique -- 
after all, email *IS* unique.

You can use id's as an index so that MySQL can find the records 
quicker, but removing redundant data  is not "normalizing". 
"Normalizing" is using remote keys to reduce data redundancy.

For example, if you have table containing people Names and their 
Professions, you might be well advised to have two tables, namely: 
One holding the names of the people (i.e., People Table); and One 
holding the type of professions (i.e., Profession Table), such as:

Profession-Table
ID  Type
1.  Mailman
2.  Fireman
3.  Policeman

People-Table
ID  Name  Profession_ID
1.   tedd   1
2.  Sam   1
3.   Jane   3
4.   Bill   2
5.   Joe   1

and so on --You see,  that is "normalized".

Cheers,

tedd

-- 
-------
http://sperling.com/