Hi Ben, It sounds like what you're looking for is an ENUM value:
http://dev.mysql.com/doc/refman/5.0/en/enum.html Bear in mind when using this data-type that if you do want to add a new value (such as a new state/country), you will have to perform an ALTER TABLE statement, which can take some time to execute over a table storing a lot of data. Using the relational method means you'd just have to add one row to a table, which is significantly faster. Thanks On Mon, 2008-09-22 at 17:12 -0400, Ben A.H. wrote: > Hello, > > We are setting up a relatively common web application which collects "user > information"... Right off the bat our system will have over 200,000 USER > RECORDS so having an efficient database & lookup scheme is critical. > I am a programmer/developer with some education in databasing but my forte > is programming hence this odd (I think) question... > > Obviously we'll have the standard USER table & a bunch of supporting tables. > > For items like STATE/PROVINCE standard database logic would dictate I setup: > > USER > name > email > .....etc... > StateID (foreign key) > > > STATE > StateID > StateName > > But I often wonder if there's any benefit in having a State table... > > For one, new States/Provinces are not that likely, removal of these entities > is also unlikely (except maybe Quebec :-)) so the chances of having to make > alternations to the State table near nil. It raises the question of whether > or not a State Table is even necessary. > Using a traditional SQL State table, I'd have to do an SQL query to populate > User State/Province options every time the "New User Registration" form is > shown - isn't this needless overhead?! > Would my webforms not load faster if State/Province information was > hard-coded as options for a combobox? Is this something people have > experimented with? > > 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)... > > ThanX in advance > Ben > > > -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
