 | | |||||
Welcome, Guest! Ready to make a name for yourself in the domain business? We welcome both the hobbyist and professional domainer to join the discussion as part of the NamePros community.
Click here to create your profile to start earning reputation for posting, and trader ratings for buying & selling in our free e-marketplace. Build your trader rating with each successful sale. Our system has tracked over 100,000 sales and counting!
Click here to create your profile to start earning reputation for posting, and trader ratings for buying & selling in our free e-marketplace. Build your trader rating with each successful sale. Our system has tracked over 100,000 sales and counting!
| ||||||||
| Programming PHP, Perl, Ruby on Rails, AJAX, HTML, XHTML, CSS, JavaScript, MySQL and any other coding topics. |
 |
 |
| | LinkBack | Thread Tools |
01-06-2007, 01:00 PM
| THREAD STARTER #1 (permalink) |
| Senior Member Join Date: May 2005 Location: Ontario Canada
Posts: 3,088
     | Database normalization help.. Hey i read about database normalization and all..and as everyone knows i am PHP noob  lol...i am making a script..a video script and i wrote the database structure that i would makke..but i just wanted all the Gurus here to take a few mins to look at it and improve on it..or give me suggestions and make fun of my noob skills lol heres the structure:????: NamePros.com http://www.namepros.com/programming/277822-database-normalization-help.html Code: funny_media users - id, user_name,user_email, password, active , timestamp, ip PM - id, user_id, message users_upload - id, user_name,num_of_uploads Config - site_name, admin_email videos - id,user_name,title,cat,sub_cat,file_name,active,views , timestamp flash - id,user_name,title,cat,sub_cat,file_name,active,views , timestamp pictures - id,user_name,title,cat,sub_cat,file_name,active,views, timestamp jokes - id,user_name,title,cat,sub_cat,joke,active,views, timestamp comments - id,video,active,user_name
__________________
Last edited by unknowngiver; 01-06-2007 at 01:10 PM.
|
|
|
01-06-2007, 03:20 PM
| THREAD STARTER #2 (permalink) |
| Senior Member Join Date: May 2005 Location: Ontario Canada
Posts: 3,088
| heres an updated one: Code: funny_media config - site_online, site_videos, site_flash, site_pictures, site_jokes, site_name, admin_email users - id, username, user_level, password, email, age, uploads, ip, active, joinstamp, timestamp pm - id, to_username, from_username, message, status, timestamp comments- id, item_ids, username, cat, active, timestamp videos - id, username, title, cat, sub_cat, filename, active, views, rating, votes, timestamp flash - id, username, title, cat, sub_cat, filename, active, views, rating, votes, timestamp pictures- id, username, title, cat, sub_cat, filename, active, views, rating, votes, timestamp jokes - id, username, title, cat, sub_cat, joke, active, views, rating, votes, timestamp rating - id, username, item_id, section, timestamp active_guests - id, ip,timestamp, page active_users - id, username, timestamp, page
__________________ |
|
|
|
01-06-2007, 03:35 PM
| #3 (permalink) |
| New Member Join Date: Jan 2007
Posts: 8
| A couple quick suggestions to get closer to "third normal form" or whatever the proper term is (I go by logic, not by fancy terms lol) instead of using username as the "foreign key", you might change to userid that links to "primary key" id of users table ex. videos - id, userid, title, cat, sub_cat, filename, active, views, rating, votes, timestamp videos.userid -> to users.id abstract your categories/sub-categories to a separate table... categories - id, name, parentID example entries would be like... 0, Parent, -1 1, Sub 1, 0 2, Sub 2, 0 3, Sub Sub 1, 1 4, Sub Sub 2, 1 5, Sub Sub 3, 2 Would give you Category Tree of... Parent |Sub 1 |-Sub Sub 1 |-Sub Sub 2 ????: NamePros.com http://www.namepros.com/showthread.php?t=277822 |Sub 2 |-Sub Sub 3 You might then consider combining all media into one table then (since they all have the same data), and having a Parent category for each of the media types. |
|
|
|
01-06-2007, 04:29 PM
| THREAD STARTER #4 (permalink) |
| Senior Member Join Date: May 2005 Location: Ontario Canada
Posts: 3,088
| hey i got what you said abt the category and sub category but i dont understand this part: instead of using username as the "foreign key", you might change to userid that links to "primary key" id of users table ex. videos - id, userid, title, cat, sub_cat, filename, active, views, rating, votes, timestamp videos.userid -> to users.id can u explain it a lil more please?
__________________ |
|
|
|
01-06-2007, 06:43 PM
| #5 (permalink) |
| New Member Join Date: Jan 2007
Posts: 8
| In databases, a field that references a record in another table is called a foreign key And the field (or combination of fields) that uniquely identifies each record in a table is called a primary key and in your case, I'm guessing that you want to store the username so you can use it reference an entry in the users table... so instead of using the username which is a text string, it might be easier and take less space to store the user's id number instead... ????: NamePros.com http://www.namepros.com/showthread.php?t=277822 the main thing here is that the id will always be unique and will never change (the username too, but perhaps you may want to be able to edit the username) let me know if you need further explanation  |
|
|
|
01-06-2007, 08:50 PM
| THREAD STARTER #6 (permalink) |
| Senior Member Join Date: May 2005 Location: Ontario Canada
Posts: 3,088
| (the username too, but perhaps you may want to be able to edit the username) this part explained it all  thanks alot so anything else that i can modify to make the database more organized?
__________________ |
|
|
|
01-06-2007, 10:24 PM
| #7 (permalink) |
| NamePros Member Join Date: Oct 2006
Posts: 65
| Config table is wrong. You would want to make your column names something like: option, value, defaultValue, status(enabled, disabled, ...), datetimeModified, modifiedById For PM: recipientId, datetimeSent, datetimeRead, subject, status (read, deleted, unread)
Last edited by HoodStar; 01-06-2007 at 10:27 PM.
|
|
|
|
01-06-2007, 11:02 PM
| THREAD STARTER #8 (permalink) |
| Senior Member Join Date: May 2005 Location: Ontario Canada
Posts: 3,088
| Config table is wrong. You would want to make your column names something like: option, value, defaultValue, status(enabled, disabled, ...), datetimeModified, modifiedById explain please
__________________ |
|
|
|
01-07-2007, 01:36 AM
| #9 (permalink) |
| NamePros Member Join Date: Oct 2006
Posts: 65
| You kind of have your config table backwards....like.... ????: NamePros.com http://www.namepros.com/showthread.php?t=277822 Its not a table of Sites and Emails...(or is it?) Its a configuration table for your application...so its a table of VARIABLES and VALUES. Code: variable | value | defaultValue SITE_NAME yoursite.com EMAIL admin@yoursite.com |
|
|
|
01-07-2007, 08:43 PM
| THREAD STARTER #11 (permalink) | ||||
| Senior Member Join Date: May 2005 Location: Ontario Canada
Posts: 3,088
|
if i have it like this: 0, Parent, -1 1, Sub 1, 0 2, Sub 2, 0 3, Sub Sub 1, 1 4, Sub Sub 2, 1 5, Sub Sub 3, 2 it would mean..that the [ID] represends the [Parent ID]...so i would have to know the ID to make a subcategory right? for example this means: 3, Sub Sub 1, 1 that Sub Sub 1 is a category under whatever is "1" in that database.. also do i need this to see where the user currently is [whose viewing what page..like in vbulletin..u can tel whose where at any time..] active_guests - id, ip,timestamp, page active_users - id, username, timestamp, page ?? and should i make a seprate table for "ratings" or have it as a field in the table "media" ?
__________________ | ||||
|
|
|
01-22-2007, 06:27 PM
| #13 (permalink) | ||||
| NamePros Member Join Date: Oct 2006
Posts: 65
|
Im not sure about your last question. I mean I know of ways to do it, but I cant advise you on the BEST way to do that. If I was doing it though, I wouldnt store that information in the database, I would probably cache it. But of course some caching does use databases so...eh. | ||||
|
|
