January 29th, 2003, 11:57 AM
database normilization (how my tables...)
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
There is a ton of stuff here, address, e-mails, urls, etc.
usrMsgrs (to track what instant messangers the user has)
msgrs (actual messangers to be selected for usrMsgrs)
imgs (storage of user images)
usrFaves (keeps track of other users this user has bookmarked)
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.
January 29th, 2003, 03:20 PM
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:
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:
Explain your problem instead of asking how to do what you decided was the solution.