#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2003
    Posts
    2
    Rep Power
    0

    database normilization (how my tables...)


    Hello,

    I've been trying to learn how to normalize a database, and I'm not quite sure if I've got it or not... The database I'm trying to create is essentially keeping track of alot of user information...

    Tables I have so far

    usrAuth
    -------
    usrId
    usrName
    usrPass

    ursInfo
    -------
    There is a ton of stuff here, address, e-mails, urls, etc.

    usrMsgrs (to track what instant messangers the user has)
    --------
    usrId
    msgrId
    msgrUsrName

    msgrs (actual messangers to be selected for usrMsgrs)
    -----
    msgrId
    msgrName
    msgrUrl

    imgs (storage of user images)
    ----
    usrId
    imgId
    image

    usrFaves (keeps track of other users this user has bookmarked)
    --------
    usrId
    favUsrId

    So thats what I have so far, now on to my question(s) lets take the "usrFaves" table for instance say there are 5000 users and each of those users bookmarks 10 people to add to their favorite users, then I would have 50,000 records in the usrFaves table... is that efficient or do I have this all wrong?

    Now for the other fields in the usrInfo table... for instance their are fields for memberSince, lastSeen, and lastUpdated, from my understanding this is a particular subject "dates and times" and should be put into it's own table??? other similar things would be descriptions, hobbies, flags for particular services to be on or off, total views this user has had, and total views for today, and finally a matching system that will allow users to select their interestes and specify the interests they would like to match too...

    So in the end I'm guessing that each of these particular "subjects" should have their own table, however I can't help but questioning if this is "proper" or not.

    Thanks!
  2. #2
  3. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1100
    I've never seen normalization having anything to do with "subjects". Simply put, don't store information more than once. Your usrFaves examples is perfect: one user will have many favorites. Yes you might get a lot of records in that table, but a simple query will narrow it down to the favorites for a particular user; quickly if you have things indexed correctly.

    I would put your usrAuth and usrInfo tables together b/c separating information that is only stored once just makes you have to query the database more. Let's look at it like this:

    table.users
    id
    login
    pass
    first_name
    last_name

    table.addresses
    id
    user_id
    street
    city
    state
    zip

    table.emails
    id
    user_id
    email

    table.favorites
    id
    user_id
    favorite_user_id

    This assumes that you'll allow users to add multiple emails and mailing addresses. If not, then you would take the fields from one of those tables and add them into the user table. For example, if you only let your users give you one mailing address:

    table.users
    id
    login
    pass
    first_name
    last_name
    street
    city
    state
    zip
    # Jeremy

    Explain your problem instead of asking how to do what you decided was the solution.

IMN logo majestic logo threadwatch logo seochat tools logo