Timothy, Definately follow the advice that Shawn gave you. Doing it this way will make it easy to have any number of emails per person without have to know how many beforehand . Here's an example below:
Table USERS: userid=15 fname='Timothy' lname='Luoma' Table EMAILS: userid=15 email='[EMAIL PROTECTED]' userid=15 email='[EMAIL PROTECTED]' Now to get your name you'd do: select fname, lname from USERS where userid=15; This would return: 'Timothy' 'Luoma' Or to get the emails: select email from emails where userid=15; This would return: '[EMAIL PROTECTED]' '[EMAIL PROTECTED]' Or to combine it together: select a.fname, a.lname, b.email from users a, emails b where a.userid=b.userid; This would return: 'Timothy' 'Luoma' '[EMAIL PROTECTED]' 'Timothy' 'Luoma' '[EMAIL PROTECTED]' Basically what I'm trying to say with my example is you have one table(USERS) that holds a key(userid) which you can use to access all the other tables. On Mon, 18 Oct 2004 14:16:12 -0400, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > There are numerous advantages to going with the "multiple" table database > you described. That is what we call a "normalized" data structure. Try > searching again for terms like "normalized", "normalizing", and "normal > form" for additional background. Add the terms "tutorial", or "overview" > to find web sites that have more explanations than theory. Keep following > links until you either hit a brick wall or a breakthrough. > > The whole practice of normalization was pioneered in the seventies by > Boyce Codd. Even if he didn't invent everything about it (well, he almost > did!), he is considered one of the primary fathers of relational > databases. > > Come back to the list if you have any specific questions. Normalization is > a tool/technique that all new database designers should incorporate into > their designs as early as possible. Failure to do so can lead to some > horrible design choices that make your database both hard to maintain and > impossible to expand. However, as important as it is, many people have a > hard time understanding the concepts involved. Please come back to the > list if you run into problems. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > > Timothy Luoma <[EMAIL PROTECTED]> wrote on 10/18/2004 02:02:31 PM: > > > > > > > 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] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
