Beginner Database Question
Moderator: General Moderators
Beginner Database Question
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
The other data in my product table would be description, title, image path, price.
What would be the best way to achieve this?
TIA
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
Beginner Database Question
Yah, pretty much a common pool of scents but most items are only available in 3 to 8 of all the scents.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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...)
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...)
Beginner Database Question
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?
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?
Beginner Database Question
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.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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
feyd's SET suggestion is essentially the same thing:
http://dev.mysql.com/tech-resources/art ... atype.html
Beginner Database Question
I googled and got that exact same page! Seems like probably the way to go for something this size.
Beginner Database Question
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.
Beginner Database Question
got it going with php explode function.
thanks again for the help!
thanks again for the help!