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/
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
