[SQL] Opinions?

Post by **Gogeta70** » 17 Nov 2008, 17:54

Ok guys, i've been working on making my own image gallery, and so far i've got a nice template going, and just now, i thought up a good structure for the MySQL database, but i hate it when i'm halfway into coding a project and i realized i fucked up one of the sql tables, or something like that. So what i'm asking you guys is to review this table layout i've typed up, and tell me if i'm forgetting anything, or if you think i should implement some handy feature that you think of.

Mainly, i just want you all to check me and make sure i'm good to go before i code my ass off only to hit a road block...

Here it is:

Code: Select all

images table

id - image id (2 purposes: count total images, show latest images)
uid - user id
gid - gallery id
img - image data
res - resolution
ext - image extention (jpg, gif, png)
fn - original file name
pvt - private image? (yes, no)

thumbs table

uid - user id
gid - gallery id
img - thumbnail image data
ext - image extention
pvt - private image? (yes, no)

users table

uid - user id
name - user name
pass - password (md5 hashed)
frds - friends list (access to private galleries)
ip - ip address
ldate - last login date
date - signup date
email - email address

gallery table

uid - user id
gid - gallery id
name - gallery name
count - count of images in gallery
pvt - private gallery? (yes, no)

Gallery table: used when a person clicks on a persons gallery link, also images are categorized using this table.

User table: contains user information

Image table: contains the full sized image data and information

thumbs table: contains the thumbnail image data of the images in the image table.

If you have any questions, feel free to ask.

Edit: Sneak peak at the template

: http://img88.imageshack.us/my.php?image=screeny2rd7.jpg

Post by **ayu** » 18 Nov 2008, 00:26

Excellent work so far =o

But, imo you could add 'date' to more places, like img ... so that users can check when the image was added and so on.

Anyway, keep up the good work ! ^^

Post by **bad_brain** » 18 Nov 2008, 05:28

yeah, real nice...

one feature that might be useful is to count the downloads of each image, you could show the count as extra info for each image and also have a list of most popular images.
so a download_count value in the images table would be my suggestion...

Nerdz · Post by **Nerdz** » 18 Nov 2008, 06:36

The only thing that don't work in my head is the friendlist.

When you'll add friend, you'll add them for 1 user... So 1 user can have only 1 friendlist, else it will duplicate the data way to much. So I guess you need a FriendList table and in User you'll have a friendlistID.

Post by **Gogeta70** » 18 Nov 2008, 13:15

Good idea cats, bad_brain!

Nerdz, the friends list is there so that when a user creates a gallery, they have the choice to make it public or private. Public is anybody can view the gallery. Private is only people on the friends list can view the gallery.

Nerdz · Post by **Nerdz** » 18 Nov 2008, 13:21

I know that. I wanna know how you will add people to the friend list

Post by **pseudo_opcode** » 19 Nov 2008, 14:25

dude.. time for Boyce-Codd normal form lesson.... j/k

i see redundancy..
why do you need a separate table for thumbnails,
add tid(thumbnail id) and thumbnail data to image table, and once you delete it it will automatically delete the thumbnail data too.. preventing clutter, keep the extension and private attributes same as main image.. also you can keep the thumbnail table separate, but with only two fields tid, and image data, that will ensure all three forms of normalization...
unless you have some purpose..

what about image tags? no tags?

Gogs man i can tell by looking at a design if its yours, you have your own trademark..kinda signature.. the color schemes, layout, borders.. etc

Post by **bad_brain** » 19 Nov 2008, 14:51

yeah, typical gogeta-style, real nice....just do me a favor and don't name the header image "banner.jpg" again like you did on earlier sites already....because many ad filter apps will block it then...like it happened to me...

Post by **Gogeta70** » 19 Nov 2008, 15:33

Hey, that's a good idea for the thumbs, pseudo! As for image tags, i'll look into it, i may add it in or not, i know it wouldn't be too difficult at this phase in coding it...

So, i've really developed a style for my graphics, huh? Cool, my own invisible signature

Thanks for the reminder bad_brain, i'll rename the header for ya

Oh, and people will be able to add friends 2 different ways: from their own profile, or from the other person's profile (a add friend link, i think).

Post by **pseudo_opcode** » 19 Nov 2008, 21:20

how about comments? i'll feel handicapped if i m unable to comment on an awesome pic!! Man you got to have comments...and probably a quick thumbs up and thumbs down button, like they have +/- on bash.org

oh i thought you hated databases

Post by **Gogeta70** » 20 Nov 2008, 15:38

Yeah, i'm going to add comments, i just forgot to introduce that part while planning the database layout. Also, i think a rating system would be pretty good.

I did hate databases, but god ran this query on me:

Code: Select all

INSERT INTO Gogeta70_brain VALUES (1337, 'MySQL')

Post by **pseudo_opcode** » 20 Nov 2008, 23:39

lol, god was being inefficient, if i were god i would rather do

Code: Select all

UPDATE gogeta70_brain SET 'mysql' where database='flat files';

you see that will remove 'flat files' from your database completely, replaced by mysql,

now you have flat files and mysql both in your database,
so your mind uses the first rule it encounters, like, say linux does with config files(i m sure it runs linux and not windows, if it does windows.. god should reformat)
so if you do sort by, it will show flat file db first..
see.. inefficient.. and wastage of space

[/endcheesyjokes]

Post by **Gogeta70** » 21 Nov 2008, 14:17

I can only laugh to that...