I have been tinkering with MySQL long enough to suit what modest needs I have had, but now I need to setup a new DB that is going to have more information in it, and I want to make sure that I am doing it the most efficient way for the long term.
Surprisingly, I have not been able to find a good resource for this, which means that I am either asking Google the wrong question, or this is something that everyone else seems to understand. If the former, please accept my apology and feel free to point me to any FM that I might RT.
The DB that I need to design is fairly simple, and will contain things like: Last Name, First Name, Middle Name, Preferred Name, Street Address, City, State, Zip, Home Phone, Work Phone, Cell Phone, Birthday (month and date, only a few gave years), Denomination, Church Name, email address (most have only 1, a few have 2), Program Name, Graduation Year, Group Name, and AIM Screen Name.
For the vast majority of the people in the DB, I have all the above information above. The plan is to be able to pull out either all the information into one really big printout, or be able to ask for things like just names & email, or just names & phone numbers or just names & people from a specific program & a specific year, etc.
My first inclination was just to make one table with all of the information as fields to that one table (assigning everyone a UniqueID).
Then I wondered if I should break it out into several tables, i.e. a table for name with fields First, Middle, Preferred, Last and a table for address with fields street, city, state, zip
The advantage to the first (one big table) seemed to be easier to create, but I wondered if it would be difficult to add things later (i.e. if I need an entire new column or something like work address [street, city, state, zip])?
The advantage to the second (many tables) seemed to be that it would be easier to add/remove things... but it would take effort to make sure that everything tied back to the proper ID.
Is there a general consensus over which is "better"? Are there other +/- that I am not seeing in doing in one way over the other? Is there a disadvantage to having a field like "email2" or "birthyear" which would be blank for the majority of the participants?
Hopefully I'm not asking a question that's too broad to answer or comes down to just personal preference. I'm hoping there's a good definitive reason to do one or ther other.
Thanks for your time or any FAQ pointers you might share.
TjL
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
