> 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. ---- Just to clarify: Wouldn't the ALTER TABLE though just have to be run once? and on the backend? I guess what I'm asking is: would this affect web-users in any way?
> > 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]
