Page 1 of 1

Database design question : same product diferent size

Posted: Mon Apr 19, 2004 2:31 pm
by kendall
Hello,

I am building a website that will contain a database of products and i would like to get some advice on the design of the database. I am faces with a delimma of having a product item that has varying sizes. And i would like to know how best to design the database based on this factor.
Do i create a "size" table that would reference the product with the different sizes or do i treat each size as a unique product?
Kendall

Posted: Mon Apr 19, 2004 5:03 pm
by feyd
If you are storing quantity available of each, I'd go with different entries since they will likely vary...

Posted: Mon Apr 19, 2004 5:18 pm
by llanitedave
Is size the only attribute that's variable? Is it all the same type of product or different product types? Are the sizes a few standard choices, or do they vary in unpredictable ways?

Defining the actual products themselves is the first step towards answering your question. If you don't, you might find yourself stuck when you need to track something else you didn't expect.

Posted: Tue Apr 20, 2004 3:16 am
by timvw
It depends on your view of the data

Imho, the size of a product looks like a repeating group, and thus should be placed in a separate table.

Posted: Tue Apr 20, 2004 6:41 am
by magicrobotmonkey
Do the product have product numbers? use them to dictate your DB - like if its EC3420S for a small and a EC3420M for a medium I would make them separate entries. Actually, I might anyways, because as far as stock goes they have to be treated separate, right? You will need to keep track of how many of each size ar in stock

Posted: Tue Apr 20, 2004 10:12 am
by kendall
feyd,

Yeah im sort of leaning towards that aspect myself makes more sense. I was just wondering how easy it mite be to get the "sizes" should i develop an ordering system.

llanitedave,

Im not sure i will be going into depths such as quantity but rather use a in stock not in sotck boolean. different size brings a question of different price so this was the reason for the question. Putting a products of the same type but different size as a unique item seems to be a way i was looking at it...i just wanted to get some opinions on if its is an only way or appropriate way

Tim,
I dont really no the product as this is a project im trying to pitch to a potential client. im doing this based on what i know. the clients website is http://www.mileagemack.com they sell tyres and rims. And we know rims and tyres comes in different sizes. Im not sure how far the client is willing to go but im hoping that my solution would be fitting

magic,
It would be obvious that the product would have id numbers. I agree with what youre saying and it was the evident idea. i myself would be trying to define the product data to be best suited

Thanks all for you input any furhter advice would be greatly appreciated

Kendall

Posted: Tue Apr 20, 2004 10:22 am
by JayBird
Hi Kendall,

I really recommend you read this article over at webmonkey.

http://hotwired.lycos.com/webmonkey/bac ... rial3.html

It doesn't really get relevant until lesson 2, but take 20mins to read it all and i am sure you will find the best solution.

Personally i would have different tables that are related to each other as described in the lessons.

Let us know what you think.

Mark

Posted: Tue Apr 20, 2004 10:23 am
by malcolmboston
Bech100 wrote:Personally i would have different tables that are related to each other as described in the lessons.
reading the whole topic, i believe this wuold be the best situation for many reasons including ease of administration

Posted: Tue Apr 20, 2004 11:49 am
by McGruff
Always start with a properly normalised database. It's much more flexible when ideas or specs change during development (and after).

You can of course deliberately de-normalise a db (the phpBB posts table eg) but you should only do that if you are fully aware of the implications. Database updates in particular can become much trickier if you have the same piece of data repeated in several locations.

There's an excellent O'Reilly pdf available on the net titled: "Java Database Best Practices" (I think) which I'd recommend googling down (sorry don't have my bookmarks available right now).

Posted: Tue Apr 20, 2004 1:27 pm
by llanitedave
I don't know if it's too basic for you or not, but I wish I had read it before I built my first big database:
"Database Design for Mere Mortals", by Michael J. Hernandez.

Posted: Tue Apr 20, 2004 1:41 pm
by kendall
llanitedave,

LOL@too basic for me....man it not a matter of basic i just get caught up in the ora of try to come up with the best solutions that i just dont know what it is.

I think im gonna go with the idea of having each products' size as a unique product. as far as im seeing it creates a more defines way of dealing with the differing size and price

KEndall
Thanks for all your help guys

Posted: Thu May 13, 2004 5:52 am
by dave420
I had a table of products, and a table of different types of size choice. S/M/L/XL, 12/13/14/15 etc. I had a relationship between the products and the type of size choice that was relevant for that particular product (so sweaters got S/M/L/XL and shoes got 12-15). That fitted in nicely with my templating engine, and the database was very fast. If you find that a product's size range have outgrown their specified size choice, you can make a new choice, and move that one product onto it. That way, you're not repeating S/M/L/XL for every single t-shirt or sweater you're selling.