[advanced search]
 

Go Back   NamePros.com > Discussion > Web Design & Development > Programming
Reload this Page Database normalization help..
Register Forum Rules FAQ Mark Forums Read

Programming PHP, Perl, Ruby on Rails, AJAX, HTML, XHTML, CSS, JavaScript, MySQL and any other coding topics.


Closed Thread
 
LinkBack Thread Tools
Old 01-06-2007, 12:00 PM   #1 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
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:

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 12:10 PM.
unknowngiver is offline  
Old 01-06-2007, 02:20 PM   #2 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
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
unknowngiver is offline  
Old 01-06-2007, 02:35 PM   #3 (permalink)
New Member
 
Join Date: Jan 2007
Posts: 8
0.00 NP$ (Donate)

plong0 is an unknown quantity at this point
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
|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.
plong0 is offline  
Old 01-06-2007, 03:29 PM   #4 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
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?
unknowngiver is offline  
Old 01-06-2007, 05:43 PM   #5 (permalink)
New Member
 
Join Date: Jan 2007
Posts: 8
0.00 NP$ (Donate)

plong0 is an unknown quantity at this point
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...
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
plong0 is offline  
Old 01-06-2007, 07:50 PM   #6 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
(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?
unknowngiver is offline  
Old 01-06-2007, 09:24 PM   #7 (permalink)
NamePros Member
 
Join Date: Oct 2006
Posts: 66
0.00 NP$ (Donate)

HoodStar is an unknown quantity at this point
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 09:27 PM.
HoodStar is offline  
Old 01-06-2007, 10:02 PM   #8 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
Config table is wrong. You would want to make your column names something like: option, value, defaultValue, status(enabled, disabled, ...), datetimeModified, modifiedById
explain please
unknowngiver is offline  
Old 01-07-2007, 12:36 AM   #9 (permalink)
NamePros Member
 
Join Date: Oct 2006
Posts: 66
0.00 NP$ (Donate)

HoodStar is an unknown quantity at this point
You kind of have your config table backwards....like....

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
i mean then you can add other configuration options. you wont need to add columns to your table.
HoodStar is offline  
Old 01-07-2007, 10:25 AM   #10 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
oh okay got it Thanks +rep to everyone who helped out so far
unknowngiver is offline  
Old 01-07-2007, 07:43 PM   #11 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
Quote:
Originally Posted by plong0
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
|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.
hey just thought of something
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" ?
unknowngiver is offline  
Old 01-14-2007, 01:14 AM   #12 (permalink)
Senior Member
 
Join Date: May 2005
Location: Ontario Canada
Posts: 2,928
1,675.13 NP$ (Donate)

unknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to beholdunknowngiver is a splendid one to behold
bumpy
unknowngiver is offline  
Old 01-22-2007, 05:27 PM   #13 (permalink)
NamePros Member
 
Join Date: Oct 2006
Posts: 66
0.00 NP$ (Donate)

HoodStar is an unknown quantity at this point
Quote:
Originally Posted by unknowngiver
hey just thought of something
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" ?
You would have to know the Id of the Parent, to put into the ParentId column of your category.

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.
HoodStar is offline  
Closed Thread

« PHP Notice [error] | Get the FULL url in php [including "id=1" part..] »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Site Sponsors
Advertise your business at NamePros

All times are GMT -7. The time now is 11:16 PM.

Contact Us - NamePros.com - Archive - Privacy Statement - Top

Powered by: vBulletin® Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.3.0
Template-Modifications by TMS
vBCredits v1.4 Copyright ©2007 - 2008, PixelFX Studios

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85