Page 1 of 1
Beginner Database Question
Posted: Fri Mar 23, 2007 2:47 pm
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
Posted: Fri Mar 23, 2007 2:53 pm
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"?
Beginner Database Question
Posted: Fri Mar 23, 2007 2:59 pm
by rssajkow
Yah, pretty much a common pool of scents but most items are only available in 3 to 8 of all the scents.
Posted: Fri Mar 23, 2007 3:07 pm
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...)
Posted: Fri Mar 23, 2007 3:08 pm
by feyd
I think a SET field would be most efficient, but that only works up to 64 choices. Is that enough?
Beginner Database Question
Posted: Fri Mar 23, 2007 3:16 pm
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?
Posted: Fri Mar 23, 2007 3:27 pm
by feyd
A SET can work perfectly for your needs.
Beginner Database Question
Posted: Fri Mar 23, 2007 3:28 pm
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.
Posted: Fri Mar 23, 2007 3:29 pm
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
Beginner Database Question
Posted: Fri Mar 23, 2007 3:32 pm
by rssajkow
I googled and got that exact same page! Seems like probably the way to go for something this size.
Beginner Database Question
Posted: Mon Mar 26, 2007 3:39 pm
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.
Posted: Mon Mar 26, 2007 4:06 pm
by feyd
Take a look at the SHOW CREATE TABLE, SHOW COLUMNS and DESCRIBE query syntaxes.
Beginner Database Question
Posted: Mon Mar 26, 2007 11:00 pm
by rssajkow
got it going with php explode function.
thanks again for the help!