Forum Moderators: open
I'm thinking about how to structureproducts in my database. I want to set it up as efficient as possible at the start so this will save me time in the future. How I'm thinking about setting it up:
In my industrie (strollers) there are often mainmodels which are available in lots of collors. Because they are treated as different products with each its own ean code, every color gets its own product information. Sometimes, all these colors have the same accessoiries (like a black umbrella or something), other times each color stroller has its own coller accessoiries and more often its a combination of both.
I'm thinking of setting up my internal ID's like this:
<b>ID-------Name------Categorie</b>
1000-----Black "something" stroller----Stroller
1001-----Red "something" stroller-----Stroller
1002-----Blue "something" stroller-----Stroller
1100----Black "Umbrella" accessorie----Stroller accessorie
1101----Red "Umbrella" accessorie----Stroller accessorie
1102----Blue "Umbrella" accessorie----Stroller accessorie
1110----Universal stroller accessorie----Stroller accessorie
Problem 1: For product 1000 I want to be able to "cross sell" 1100 and 1110. This can be done manualy in a special table whre I link each id alone. Or, do you guyz think there is a better way to automize this?
Problem 2: When I want to show a list of different strollers I only want a specific moddel to show up once with small foto's of other colors in the information which can be seen underneath the foto of all the different types. In this set up it would need to show all products with categorie "Stroller" (so far pretty straight forward), but it also only needs to show 1 model, for instance the black one (1000). Is it smart to make id numbers by the thousand and give each main product for its type a 1000th id? (for instance id: 1000, 4000, 12000 etc..)? Is it possible to extract only strollers that have these numbers?
I can understand this is not very clear at once, if you have any further questions, please let me know!
Thanks!
I don't know your subject domain (strollers), but I'm guessing you might sell more than one model and probably multiple models from multiple manufacturers each with their on accessories - some color specific, some universal. I could even see major differences in how you might need to represent the data differently depending on the manufacturers. For example, one manufacturer may have a single model # for a stroller but make it available in 5 different colors (same model#, different color attribute). Another manufacturer might have a stroller that is available in 5 colors but they represent them as different model numbers instead of a single model number + color attribute.
How you represent which is the default color stroller to show could be done many ways depending on the schema you come up with. You might represent it with a column that says DefaultColor Y/N. Or a row in a table may indicate this (instead of a column). Hard to say what's best until you flush out more details.
These are just some of the things you need to have a grip on and understanding of before you sit down to figure out what your DB schema/table structure will look like.
[edited by: ZydoSEO at 8:44 pm (utc) on Nov. 26, 2008]
This means I have to hook up every product manually (which isnt a complete disaster because it can be done pretty quickly and the personal touch gives some extra checks). However, How I normally did this with very small databases is like this:
ID CombinationID
1000------2000
1000------3000
1000------5000
etc till all product ids are linked to product 1000 that I want.
2000------1000
2000------3000
2000------5000
As you can see, when I already linked 1000 to product 2000 I also have to do this for product 2000 and then link it to 1000 in this case. (On the product page the ID of the product is used to look up the ID colum and then the CombninationID numbers that are attached.)
Is there a better way of doing this?
