Database design question : same product diferent size

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Database design question : same product diferent size

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If you are storing quantity available of each, I'd go with different entries since they will likely vary...
User avatar
llanitedave
Forum Commoner
Posts: 78
Joined: Thu Jan 15, 2004 11:24 am
Location: Las Vegas, NV.

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post 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
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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).
User avatar
llanitedave
Forum Commoner
Posts: 78
Joined: Thu Jan 15, 2004 11:24 am
Location: Las Vegas, NV.

Post 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.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post 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
dave420
Forum Contributor
Posts: 106
Joined: Tue Feb 17, 2004 8:03 am

Post 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.
Post Reply