On Mon, Sep 22, 2008 at 5:12 PM, Ben A.H. <[EMAIL PROTECTED]> wrote: > > There are various other fields that I believe could be handled like this > for > 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 > to > 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)... > Hi Ben,
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 web scripts. 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. Dave.
