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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
