Database normalization help..

[unknowngiver]

Hey
i read about database normalization and all..and as everyone knows i am PHP noob:p 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 :p lol heres the structure:

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

 

[unknowngiver]

heres an updated one:

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

 

[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.

 

[unknowngiver]

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?

 

[plong0]

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

 

[unknowngiver]

(the username too, but perhaps you may want to be able to edit the username)
this part explained it all :D thanks alot
so anything else that i can modify to make the database more organized?

 

[HoodStar]

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)

 

[unknowngiver]

Config table is wrong. You would want to make your column names something like: option, value, defaultValue, status(enabled, disabled, ...), datetimeModified, modifiedById
explain please

 

[HoodStar]

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.

variable      |     value      |     defaultValue
SITE_NAME                            yoursite.com
EMAIL                                [email protected]

i mean then you can add other configuration options. you wont need to add columns to your table.

 

[unknowngiver]

oh okay got it Thanks +rep to everyone who helped out so far

 

[unknowngiver]

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]

bumpy

 

[HoodStar]

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.