Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: David Ashley (dashleylistgmail.com)
Date: Tue Sep 23 2008 - 10:00:34 CDT
On Mon, Sep 22, 2008 at 5:12 PM, Ben A.H. <bencanview.com> wrote:
> There are various other fields that I believe could be handled like this
> a cumulative performance boost. For example: country, state/province,
> gender, industry, occupation, ethnicity, language are all options that
> aren't going to change that often. Then again, when we do have to display
> the users choice; for example if user.countryID=3 we'd have to have a way
> turn that number 3 into the word "Canada" when we display the users
> profile... I'd probably do this via XML lookup.
> Has anyone experimented with the benefits/tradeoffs of such a scheme? As
> I've said, we are dealing with at least 200,000 user records, probably
> 300,000-400,000 in the next year. The User table contains at least 50
> attributes, 15 of which are Foreign Keys that link to tables that will
> likely never change (the users choices for countryID, stateID,
> OperatingSystemID, internet_connectionTypeID, internetUserFrequencyID,
> industryID, occupationID)...
This is a very common issue in web database development. The issue is
really whether the tables should exist (logically) in the database or in the
For example, for the 50 states, a lot of programmers would put this logic in
the web script and just store the two-letter postal code in the database
table (but with no separate table for "states"). The mapping from "MI" to
"Michigan" would occur in the web scripts; as would the logic to know that
"XQ" is not a state.
This is done all the time.
As long as the database is indexed properly for the queries you want to run,
there is not usually a severe performance hit. Either approach is fine; but
the web script approach is usually easier.
I just have one piece of advice for you:
Use something stored in the database that won't change when you update the
fixed tables in the web scripts. For example, a 2-letter postal state code
is fine; because when they add the 51st state they won't change any of the
existing codes. Similarly, use "M", "F", etc. for sex. Don't store
anything in the database that is tied to, for example, the integer index of
a table entry in the web script or stored in some other way where when you
modify the script you'll need to alter the database as well; otherwise
you'll be writing a separate script to remap values of columns in the
database so that you can use the new scripts.