Database Normalization (how my tables are set up)

[ossian224]

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!

 

[zemp]

Yes that sounds about right. The goal of normalization is to remove redundant information and/or empty table fields thus making your database more efficient.

For example your table usrInfo contains email addresses. But you can have someone with 0 emails or 4+ emails. So how many fields do you put in that table. NONE, create and emailaddress table linked to the usrID. This allows you to add as many emails as you need or want. You can even add an emailTypeID field linked to an emailtypes table. From which you can differenciate primary, home, work, etc. emails.

Here is a link that explains it a bit more. Search msdn for 'normalization' and you will find more information.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;283878

The basic goal is to have each piece of user information appear only once in the database, linked together with ID fields, and no empty fields in existing records. Thanks and Good Luck!

zemp