SQL structure [no trolls allowed]

[justFred]

I want to try to get it right the first time so I don't have to pull my hair out later. If you were storing FHGs, Model Information, Sponsor Information, etc in a relational database.... how would YOU set up the structure of these tables? I'm thinking something like:

main table - galleryID, sponsorID, modelID

then

gallery table - galleryID, category, number, type, dateAdded, any other information about the GALLERY

sponsor table - sponsorID, sponsorName, 2257custodianContact, siteURL, other info about each SPONSOR

model table - modelID, site, niche, hair color, blah, blah, blah about each MODEL

(Yeah I know it's a crappy structure. This will be my first database, I am showing an example and asking your opinion)

2 simple questions for each poster:

1. How would you set it up?
2. What's your experience with databases?

Thanks every1

[StuartD]

Yup, that's how you do it.

[schneemann]

It doesnt look terrible, but you need at least two more tables:
a category table for gallery
a niche table for models
and possibly a lookup table for niche

In any case whereever you'll have something that isn't unique among every record, you should store that information externally. 'niche' is a perfect example of this. You might end up with 100 chicks with big tits. if you eventually end up changing the term "Big Tits" to "Busty", you'll have to make that change in each record.

I recommend you also have a lookup table for niche, so that your models can have multiple niches. Imagine having a busty redhead. You can use that information to cater to freaks like me who go insane for big tits and red hair.

[justFred]

oh yes and another question. I am limited to approximately 7.5 million SQL queries each month on my virtual host. Would it then, be a bad idea to query the databases several times per user? I'm thinking that might ad up to a lot very quickly.

You can use that information to cater to freaks like me who go insane for big tits and red hair.

[schneemann]

Originally Posted by justFred

oh yes and another question. I am limited to approximately 7.5 million SQL queries each month on my virtual host. Would it then, be a bad idea to query the databases several times per user? I'm thinking that might ad up to a lot very quickly.

Not sure how many SQL queries you plan on using, LOL. If you use say, 100 queries per page view, then you got a big problem to deal with. I would recommend doing everything you can to make the database do the work and not the scripting language. A lot of people do these convoluted loops that would be best handled with a more well-formed query.

You should also look into caching. If, for example, a gallery never changes once it is published, it makes no sense to make constant trips to the datbase on every page view. You can cache parts of pages, too. So for example lets say you have a gallery (that never changes) and then a bunch of link exchange shit elsewhere on the page, you can cache the gallery part and still grab the link exchange results from the DB.

Originally Posted by justFred

I was thinking more along the lines of Bettie Ballhaus, but that'll do!

[polle54]

I would put a foreign key from sponsor to the gallery table so that you at least know which sponsor the gallery belongs to....

also I don't know how you are going to use it but if it was me and the sites from the sponsors were relevant I would maybe make two tables for that

programs - id int 11, name varchar(250), login_url varchar(250), ref_url varchar(250), desc text.

sites - id int(11), program_id int(11), name varchar(250), url varchar(250).

and then on your gallery table I would add a column called site_id int (11).

Maybe you want some relations to the model as well from your gallery and that is done the same way with adding a model_id field with the id of the model from the models table

Good Luck

you are on the right track mr

[justFred]

ah yes I was planning on relating the site to the sponsor, and the model to the site and/or sponsor as well.

@ schneemann : if you check out the page and refresh it, the images shuffle. Currently it's a flat file and $array = file($filename) then shuffle($array) etc.

I suppose I could query once on the page load, and send the information in a javascript array packaged with the code required to shuffle it client side...

but I am sort of attached to the 3.67% of my surfers that don't have javascript enabled. *sigh*

[schneemann]

Originally Posted by justFred

ah yes I was planning on relating the site to the sponsor, and the model to the site and/or sponsor as well.

@ schneemann : if you check out the page and refresh it, the images shuffle. Currently it's a flat file and $array = file($filename) then shuffle($array) etc.

I suppose I could query once on the page load, and send the information in a javascript array packaged with the code required to shuffle it client side...

but I am sort of attached to the 3.67% of my surfers that don't have javascript enabled. *sigh*

I would also be inclined to let the database do the shuffling and not javascript.
You might want to check into a host that doesn't limit your queries. It wouldn't hurt to figure out how many queries you're doing though. It might not be an issue.

[drjones]

If your host offers postgresql, instead of mysql you can use stored procedures for a lot of queries, that might save you on hits to the db.

Not sure what kind of support mysql has for stored procedures these days, if any.

And yea, read up on database normalization.

[schneemann]

Originally Posted by drjones

If your host offers postgresql, instead of mysql you can use stored procedures for a lot of queries, that might save you on hits to the db.

Not sure what kind of support mysql has for stored procedures these days, if any.

And yea, read up on database normalization.

Stored procedures are available as of MySQL 5.0
http://dev.mysql.com/doc/refman/5.0/...rocedures.html