Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2008
    Posts
    28

    Beginner setting up tables - atomic enough?

    Okay, first baby steps here... bear with me please. I'm working through a book on SQL while trying to figure all this out.

    First one of my tables would hold all the address information for a given member

    • AddressID
    • LastName
    • FirstName
    • MidInit
    • Suffix
    • Title
    • StreetAddress1
    • StreetAddress2
    • City
    • StateOrProvince
    • PostalCode
    • Phone1
    • Phone2
    • Email1
    • Email2


    First question is... would this be considered atomic enough for 1NF, given that there are a couple columns that hold similar information (Phone1 & Phone2, Email1 & Email2)? Second question is... will it really matter? I'm working on a club membership list for a private non-profit group, so I don't really see the list growing to huge numbers.

    Thanks,

    Monte

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Probably won't matter much, but, in many cases, up to three phone numbers are common. (Home, Work, Mobil) So, phone numbers might be a good candidate to break out.

    However, you're using an AddressID field, which implies that the table is used strictly for addresses. In that case, the name data probably shouldn't be in this table, as it really has little to do with the address.

    Will you ever have members with the same address, and/or phone numbers?

    A mobile phone number is usually associated with one individual, as are email addresses, whereas a home number is associated with an address...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Nov 2008
    Posts
    28
    Hmmm... I guess my train of thought was to have most of the information for a mailing list - some email, some hard-copy - in one table. Not really sure if thats a benefit or a liability?

    So... would I be better off just keeping everything together and perhaps renaming the table 'ContactInfo' with 'ContactID', or breaking up the info further - moving the LastName, FirstName, MidInit, Suffix, Title into a separate table 'MemberName', keeping the physical mailing address info in a table 'MailingAddress' and figuring some other way to tie in phone numbers and email addresses?

    TIA,

    Monte

  4. #4
    Join Date
    Nov 2008
    Posts
    28
    More like this?
    Attached Thumbnails Attached Thumbnails ooBase-relationships-001.jpg  

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is usually no good argument for splitting email and phone out like that, even if you can

    it does not really help matters, not in the same way that splitting out multiple order items on an order does, and it certainly makes things messier for no distinct advantage

    it isn't technically even necessary, since each email is atomic, and each phone is atomic, and therefore within the letter of the law the table actually is in 1NF with those columns included

    if you are indeed working your way through a book (and not, as many other people who say they are studying on their own, actually preparing a homework assignment), just leave them where they are, in the same table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2008
    Posts
    28
    Okay... the thing that got me to asking in the first place was where in the book (Head First SQL, fwiw) it mentioned two 'rules' about atomic data:

    1) A column with atomic data can't have several values of the same type of data in that column. Since it's possible for multiple members to have the same phone number (same family, or same employer), I started wondering if it was appropriate to break it down further. But, along the same train of thought, those people would likely have the same address information which would already be duplicated within the 'Address' table...

    2) A table with atomic data can't have multiple columns with the sametype of data. Since my original scheme had things laid out as Phone1, Phone2, Email1, Email2, etc. I thought that constituted multiple columns with the same sort of data...? With them labeled as HomePhone, MobilePhone, WorkPhone, etc. it appears that they would be, as you indicated, reasonably unique - although people may still have the same home or work phone number by chance.

    If those 'rules' are in error, I guess I'm better off finding out now, rather than later.

    if you are indeed working your way through a book (and not, as many other people who say they are studying on their own, actually preparing a homework assignment)
    I wish Been taking online courses through the local community college and state online degree program, but they don't offer anything nearly so radical as *computer* classes online This should give you a brief idea of what I'm here for. I realize on the 'Net that it's often hard to tell if anyone is really what they say they are, though.
    Last edited by memilanuk; 03-27-09 at 11:16.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the argument about whether two people have the same phone number is not nearly as tricky as the one about whether two people have the same first name

    you don't see database designers get their knickers in a knot whenever the possibility arises that two rows in the persons table might have the same first name "John" -- they just accept this as normal, with nothing needing to be done about it

    but in any case, let me clarify those two rules for you

    1) says don't put multiple values into one column on a single row (not multiple values down the column -- that's normal)

    so this would be wrong --
    Code:
    product_id product_name    supplier   keywords 
       42      frammeldingus   acme inc.  outdoor,wooden,portable
    2) this is your email1, email2, phone1, phone2 scenario

    the rule of thumb here is very simple: if you are regularly trying to target/retrieve a specific value from one of those columns, consider splitting it out

    so this would be wrong
    Code:
    product_id  salesjan  salesfeb salesmar salesapr ...
       42        12.15     24.67     2.11     9.37   ...
    here, if you need "last three months running total sales" then you're farked, you have to write a ton of code like CASE WHEN currmonth = 4 THEN salesfeb+salesmar+salesapr WHEN currmonth = 5 then salesmar+salesapr+salesmay, etc.

    if you only ever retrieve todd's phone numbers when you display todd's information, and don't need to search phone numbers (e.g. you never think of running a query to see if two people have the same phone number [why would you?]), then having two columns phone1 and phone2 is perfectly acceptable (and, just so you're comfortable, is technically within the letter of the law for 1NF)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2008
    Posts
    28
    Quote Originally Posted by r937
    1) says don't put multiple values into one column on a single row (not multiple values down the column -- that's normal)
    Ah. Gotcha.

    2) this is your email1, email2, phone1, phone2 scenario

    the rule of thumb here is very simple: if you are regularly trying to target/retrieve a specific value from one of those columns, consider splitting it out
    I see... normally I would be querying to see a) if they had an email address, so I don't have to print out a mailing label to send them a hard copy of the newsletter, b) what their phone number is in the event we need to get information out to members quickly.

    (e.g. you never think of running a query to see if two people have the same phone number [why would you?])
    Well... if more than one person has the same phone number (lives in the same house), it'd be nice to identify duplicate info like that so I only call their house *once* looking for them. We don't do the call-around bit very often, so probably not worth the trouble.

    Thanks,

    Monte

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I would always create a separate table for phone numbers if I expect to have more than one of them. Also, "Home", "Work" and "Mobile" are possible attributes of phone number and they are not mutually exclusive categories either - think about it.

    So I would generally favour a phone numbers table, with either a set of attributes for each number ("fax" and "voice" are two possible attributes of the same number for example) or maybe another table to record the phone attributes.

  10. #10
    Join Date
    Nov 2008
    Posts
    28
    Quote Originally Posted by loquin
    However, you're using an AddressID field, which implies that the table is used strictly for addresses. In that case, the name data probably shouldn't be in this table, as it really has little to do with the address.
    This one has been bugging me a bit over the last couple days. I guess I'm unclear on whether you think it's inappropriate because I used the term 'AddressID', or because it needs to be broken out for a specific technical reason? i.e. would it be alright if I simply called it 'ContactInfo', as that is what I'm really trying to do here - store the contact info, whatever it may be (name, address, phone, email) with as little fuss as possible. Our normal uses will be to print out a members list (LastName, FirstName, and then other data from another table - DateRenewed, DateExpires) for spot checks of membership on site, print mailing labels or send emails for newsletters or announcements, and... thats about it for now.

    Will you ever have members with the same address, and/or phone numbers?
    Yes, we have a number of families where everyone in the family (husband, wife, children) are members in their own right. It's not the 'norm', as generally a membership has been considered a 'family' membership by default and some people purchase extra keys for the other family members (bad scenario which has some other issues), but other people join individually for their own reasons. We have one instance of two people having the same first and last name ('Ron Smith' or something like that), and neither person filled out the M.I. field, so with our old system it got a little weird keeping them separate.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by memilanuk
    ... what I'm really trying to do here - store the contact info, whatever it may be (name, address, phone, email) with as little fuss as possible. Our normal uses will be to print out a members list (LastName, FirstName, and then other data from another table - DateRenewed, DateExpires) for spot checks of membership on site, print mailing labels or send emails for newsletters or announcements, and... thats about it for now.
    based on this statement of requirements, you can use a single table, and if you want to have 2 phone and 2 email columns, you do not need to split them out
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by memilanuk
    This one has been bugging me a bit over the last couple days. I guess I'm unclear on whether you think it's inappropriate because I used the term 'AddressID', or because it needs to be broken out for a specific technical reason?
    Here you see the unavoidable problem with attempting to do design-by-forum. If all we have to go on is a list of attribute names then the answers you get will have to be based on assumptions and guesswork.

    In this case, if we assume that AddressId is the key of this table (you didn't tell us it was) then your design seems to imply that the "address" (ie, assuming that's what "AddressId" is supposed to represent) determines the FirstName and LastName of the person, which is not necessarily true if more than one person shares the same address. However, these are only assumptions based on your attribute names and with no opportunity to analyse your actual problem. Detailed analysis is practically impossible to do in an online forum.

    I would suggest it is better to separate names and addresses into separate tables since they do not necessarily map one to one.

  13. #13
    Join Date
    Nov 2008
    Posts
    28
    Rudy,

    Thanks for the feedback.

    dportas,

    I understand what you mean by these things being difficult to convey via forum... but I'm not sure I have a better option at hand. I assumed that everyone understood I meant 'AddressID' to be the primary key for the table, used to identify a particular record and such - although there was no real reason to believe so. There's a high probability that part of whats causing some problems at this point is I'm still working out exactly how to do the primary key/ foreign key relationships and constraints to tie everything together, and as a result not expressing things the way I should. My apologies.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •