Beginner Database Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rssajkow
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 1:03 am

Beginner Database Question

Post by rssajkow »

This is a novice question. I have a site with products (soaps, shampoos, etc) that come in different scents. Not all the products come in all the scents. I need to put these into mySQL and was thinking either putting everything in a flat table with scent fields, each scent field would be a CHAR of length "1" with "y" or "n" to indicate if it comes in that scent. There are about 20 different scents and this number is not likely to increase by much. Would I be better of putting the scents in a separate table?

The other data in my product table would be description, title, image path, price.

What would be the best way to achieve this?

TIA
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

is it a common scent pool? Like you have so many scents in general that are used in lots of stuff, but there's no "lavender conditioner"?
rssajkow
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 1:03 am

Beginner Database Question

Post by rssajkow »

Yah, pretty much a common pool of scents but most items are only available in 3 to 8 of all the scents.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

if they all have unique names, you could just dump them (comma separated) into a "scents" text col - that way you're not restricted by how many scents you have, you only have one table, and you can find things by scent with LIKE='%lavender%'

It's not "normalized" and I'll likely be politely ridiculed for suggesting it, but it seems like a half-decent solution that will get your project done faster.


If you want to go the "normalized" route...

you'll need (at least) 3 tables: product_type, scent, and product:

product type: id, typeName

scent: id, scentName

product: id, productId, scentId, (and other stuff like price, size, etc...)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I think a SET field would be most efficient, but that only works up to 64 choices. Is that enough?
rssajkow
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 1:03 am

Beginner Database Question

Post by rssajkow »

I guess that beats being impolitely ridiculed!

I should have included the following - dont know how this would play in with your method: We use a third-party cart (paypal), so for each product that is available in more than one scent, we need a pull down menu that loops through the available scents for that product and allows the user to select a scent before submitting paypal's form with the "add to cart" button.

Again, I dont think overhead/scalability is a huge factor since we are talking about a "mom and pop" type site with less than 50 products and less than 20 scents.

what do you think?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

A SET can work perfectly for your needs.
rssajkow
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 1:03 am

Beginner Database Question

Post by rssajkow »

Thanks for your reply, yes I would be shocked if there was ever more than 64 scents. This sounds like a viable way as long as I can simply list the scents for each product in a pulldown. I also want to create an interface where the client can add or remove scents to certain items.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

While both would work for your purposes, I would probably cheap out and use the "good enough" method.

feyd's SET suggestion is essentially the same thing:
http://dev.mysql.com/tech-resources/art ... atype.html
rssajkow
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 1:03 am

Beginner Database Question

Post by rssajkow »

I googled and got that exact same page! Seems like probably the way to go for something this size.
rssajkow
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 1:03 am

Beginner Database Question

Post by rssajkow »

I am a new kind of stuck now. Got my DB set up, but how can I get the values from the set column into an array (as separate values) so i can create a pulldown? Having trouble finding any articles using PHP to display the values from a set. What I want to do is if there are more than one value in that products scent it displays the pull down and if not it just echoes the one value.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Take a look at the SHOW CREATE TABLE, SHOW COLUMNS and DESCRIBE query syntaxes.
rssajkow
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 1:03 am

Beginner Database Question

Post by rssajkow »

got it going with php explode function.

thanks again for the help!
Post Reply