SitePoint Sponsor

User Tag List

Results 1 to 23 of 23

Thread: tricky question

  1. #1
    SitePoint Enthusiast daliren's Avatar
    Join Date
    Oct 2001
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question tricky question

    I'm creating an online directory. In it I need the ability to have a listing posess a variable number of attributes. For instance, person A might just have one office address, while person 2 might have three. I need the ability to render this all in HTML like so:

    Person 1
    69 Dingleberry Lane,
    Anywhere, CA 95467


    Person 2
    27 Unida Pl.
    Anywhere, CA 95467

    666 Shadow Ln.
    Pits o' Hell, CA 95654

    27 My Wy.
    Wherever, CA 95623
    ...and so on. While I'm at it. One address should be designated as the primary one. Does anyone know what the best way might be to approach this? Does PEAR have anything that could tackle this problem?
    cheers,

    Darren Cassidy

  2. #2
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you just need 2 DB tables, one that hold the people and one that holds the addresses. so the tables would have a one-to-many relationship.
    you could designate in the person table which address is considered primary, etc...

    then just run a query to get the info the way you want it.

    The tables would be something like this.

    table 1:
    userID
    lastName
    firstName

    table 2:
    addressID
    userID
    street
    city
    state
    zip
    primary (y/n)

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another, better method would be have 3 tables.

    Table 1:

    UserID
    FirstName
    LastName

    Table 2:

    AddressID
    AddressLine1
    AddressLine2
    AddressLine3
    PostalCode

    Table 3:

    UserID
    AddressID
    PrimaryFlag

    That way, you could pull all data based on the one query, via Table 3 ? Database JOINs would help considerably if your well versed in Database design and analysis

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    --EDIT--

    But if you ask me, for this sort of data structure, XML would be a far better option for you.

    Where ever possible, a flat file I'm assuming would give better performance than pulling data - in your case, simple relations - from a database.

    Using PHPs DOM extension or even XPATH, it'd be easy enough

  5. #5
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol yes thats true, depends what normal form you want to go to.
    In this case using that 3rd table wouldnt save you much.
    and... I got an A+ in database design thank you very much

  6. #6
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by JRMillion
    lol yes thats true, depends what normal form you want to go to.
    In this case using that 3rd table wouldnt save you much.
    and... I got an A+ in database design thank you very much
    In which case, I do not think having the primary field as a boolean/flag on either the address table or an intersection table is the right design. Think of a person with 100 addresses. You have 99 useless "N" values for that person. Instead I would have a sinlge "primary address ID" field on the user table:

    table 1:
    userID
    lastName
    firstName
    primaryAddrID

    table 2:
    addressID
    userID
    street
    city
    state
    zip

    This would obviously work best in a RDBMS that supports referential integrity
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  7. #7
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree, I only put the flag there at the time so you could tell which addresses where primary just by looking at the address table, but that is not needed. You are correct it should go in the user table.

  8. #8
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To go for a truly normalized db structure, you probably should have Widow Makers intersection table as well. This would allow for multiple users to have the same exact address without duplicate entries in the address table You could still have the primaryAddrID field on the user table with that structure.

  9. #9
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sweatje
    To go for a truly normalized db structure, you probably should have Widow Makers intersection table as well. This would allow for multiple users to have the same exact address without duplicate entries in the address table You could still have the primaryAddrID field on the user table with that structure.
    Yes I suppose that is true, but then you would also have to check if an address already exists before you update the table.
    Last edited by JRMillion; Apr 17, 2004 at 21:36.

  10. #10
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks SweatJe Still think it could be done in XML format as well though

  11. #11
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Timtowtdi

  12. #12
    SitePoint Enthusiast daliren's Avatar
    Join Date
    Oct 2001
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys. I'm assuming you are talking about XML to render all this, such as:

    <addresses>
    <address>
    <street></street>
    <city></city>, <state></state> <zip></zip>
    </address>
    <addresses>
    cheers,

    Darren Cassidy

  13. #13
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well it would be something like this, there are a number of ways you could format it...
    Code:
    <directory>
       <person lastName="" firstName="">
          <address primary='yes'>
              <street></street>
              <city></city>
              <state></state>
              <zip></zip>
          </address>
          <address>
              <street></street>
              <city></city>
              <state></state>
              <zip></zip>
          </address>
       </person>
       <person>
         etc....
       </person>
    </directory>

  14. #14
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exactly

    Would be very easy to use PHPs DOM extension to route out your details - with PHP5 would be even easier and cleaner

    Not only that, as an added bonus you could use various XSL stylesheets to display or reformat your data.

    XML is way too cool for it's own good

  15. #15
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would the XML file be added to / updated? Through PHP DOM, without a database at all?

  16. #16
    SitePoint Wizard bronze trophy JRMillion's Avatar
    Join Date
    Apr 2004
    Location
    Arlington VA
    Posts
    2,094
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well you could use a php script to read in, modify and re-output it to the file.

  17. #17
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would have to agree with the three table solution as being the proper solution.

    The use of XML would be cool but a decision would have to be made whether it is warranted or not based upon its use and/or display.

    As an FYI MS SQL Server can take RDBMS data and output to XML (in auto-generated format based upon the column names or a customized layout).

    I need to get back into mySQL a bit...
    Last edited by StephenBauer; May 26, 2004 at 10:14.

  18. #18
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would have to agree with the three table solution as being the proper solution.


    Anything less is simply not following the basic 3 forms of database normalisation

    If your currently using PHP4 just now, you may want to think about XML at a later date, if for example, you move over to PHP5??

    Then, you can use SimpleXML to read, write, etc your XML files. Never used MS SQL so cannot comment on that point.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Widow Maker
    Where ever possible, a flat file I'm assuming would give better performance than pulling data - in your case, simple relations - from a database.
    that's simply not true

    let's say you have ten thousand addresses, and you want to pull all the addresses in a particular postal code

    with a flat file, you have to read all ten thousand records

    with a database table, indexed on postal code, you end up reading only those rows you want, whether it's seven, nine hundred and thirty seven, or nine thousand, nine hundred and thirty seven

    as far as the many-to-many intersection table is concerned, that of course is th correct approach for a many-to-many relationship

    however, i would like to suggest that people and addresses are not in a typical many-to-many relationship

    statistically, it's more like a 1.000937-to-many relationship

    the 3-table structure imposes complexity on queries needed to retrieve people and address data together, whereas the 2-table structure means simpler queries

    so using the 2-table structure does mean that you will have the same address in the database more than once, but statistically, this might increase the space required from 8,474 megabytes to 8,937 megabytes

    personally, i would opt for the simpler queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A flat file would be far slower in the context you've pointed out, but I wasn't thinking in this context.

    Something else altogether

  21. #21
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    that's simply not true
    as far as the many-to-many intersection table is concerned, that of course is th correct approach for a many-to-many relationship

    however, i would like to suggest that people and addresses are not in a typical many-to-many relationship

    statistically, it's more like a 1.000937-to-many relationship

    the 3-table structure imposes complexity on queries needed to retrieve people and address data together, whereas the 2-table structure means simpler queries

    so using the 2-table structure does mean that you will have the same address in the database more than once, but statistically, this might increase the space required from 8,474 megabytes to 8,937 megabytes

    personally, i would opt for the simpler queries
    Agreed, but the three table approach for people/addresses does have a use in given situations - for instance corporate population and address, client addresses, and/or when linking on e-mail addresses, etc.

    However, then one (i.e. you ) could argue that even so, disk space is cheap nowadays so why bother!
    Last edited by StephenBauer; May 26, 2004 at 10:14.

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    simple enough to determine

    count the number of rows

    now count the number of distinct addresses

    there's your 1.000937 ratio

    not my database, your database

    make your own decision

    just how much are you willing to pay to remove the redundancy?

    note that you don't need to worry about multiple people at the same address if there's always an easy way to find them, in those rare instances where you might wish to know the number of distinct addesses as opposed to the number of distinct people

    that's where GROUP BY comes in

    now, how much were you willing to sacrifice in increased query complexity in order to remove a few K from your multi-Meg database?

    like i said, not my decision, yours, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Addict StephenBauer's Avatar
    Join Date
    Apr 2004
    Location
    USA
    Posts
    263
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know we could argue this to the moon and back, so suffice it to say it is inddeed a personal preference on ease of query building, disk space, consistency of input (and input gui building), ease (and method) of updating addresses, etc.



    Quote Originally Posted by r937
    simple enough to determine

    count the number of rows

    now count the number of distinct addresses

    there's your 1.000937 ratio

    not my database, your database

    make your own decision

    just how much are you willing to pay to remove the redundancy?

    note that you don't need to worry about multiple people at the same address if there's always an easy way to find them, in those rare instances where you might wish to know the number of distinct addesses as opposed to the number of distinct people

    that's where GROUP BY comes in

    now, how much were you willing to sacrifice in increased query complexity in order to remove a few K from your multi-Meg database?

    like i said, not my decision, yours, eh
    Last edited by StephenBauer; May 26, 2004 at 10:13.


Bookmarks

Posting Permissions

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