SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Nov 2000
    Location
    Cheshire, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating an addess book for registered visitors

    Hey guys!

    I currently manage a greetings card site from which visitors can send cards to their friends. I'm currently developing a user registration system which uses sessions to track visitors. User details are stored in an SQL database, fields are: username (unique key), forename, surname and email address. However, I would also like to allow each user to have a personal address book associated with their account (so users can quickly select the desired recipients of their card from their address book). Could anyone suggest the easiest and most efficient method of storing the addresses? Should they exist in a new field in the users table or should I create a new table specifically for the addresses?

    Oh.. and one more thing, what other user information does anyone recommend I should collect? I was thinking some extra fields could be used for useful demographic info, eg. sex, age etc. Is is worth storing each data value within the user record or should I have a separate table keeping a running total?

    I hope I have explained myself well enough! All replies are greatly welcomed!

    Dom

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Where there is a one to one releationship then you can store the data in the same table. For example, if a user has one and only one email address then they can be in the same table. If however, a user can have one or more email addresses - then you need another table to manage that relationship.

    In strict data normalisation, you weed out any fields that only have a transitive relationship to the primary key through another field. To normalise a data structure you would take this table:

    User (id, name, streetAddress1, streetAddress2, suburb, zip, phone)

    and split it up like so:

    User(id, name, streetAddress1, streetAddress2, suburb, phone)

    Zip(suburb, zip)

    Why? Because the zip code is not related directly to the user, but is dependent on the suburb (it is the suburb that has the zip code, not the user). That's all fine and dandy, but then you might need to start looking to denormalise your data schema to enhance its real-time performance. For example, you may have all the data relating to a user in the one table. However, you may find that most of the time, your script is only querying the same three or four columns of data and the other columns are not used often. In this case you may want to aid the efficiency of those look ups by spliting the data into two tables.

    With MySQL, if you are really worried about maximising the speed of your queries, you should try and seperate out any fields that are not fixed length into a seperate table. This is because fixed length records are the quickest to look up. This means using CHAR(N) fields instead of VARCHAR(N). The cost is the extra storage space required because of the waste of having fixed length strings in the table.

  3. #3
    SitePoint Member
    Join Date
    Nov 2000
    Location
    Cheshire, UK
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help!

    Dom


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
  •