Database relationship help

artisticre

I need some help. I am trying to learn MYSQL. I have read how to do relationships but I have come across something that I am lost on and need another opinion. Here is the existing table

CREATE TABLE tshirts (
id tinyint(4) NOT NULL auto_increment,
shirtname varchar(50) NOT NULL default '',
image varchar(50) NOT NULL default '',
description varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

I want to add tshirt prices and sizes to this table. I have tshirts that have small, medium, large, XL, 2XL, 3XL, 4XL and each size has its own price. My problem I am dealing with is that not all designs are available in each size. How do I set this up to pull each size & price but also notify the user if the size isn't available?

Any coding examples would be great since I am going cross-eyed trying to figure this out.

thanks

ultraslacker

Originally posted by artisticre
I want to add tshirt prices and sizes to this table. I have tshirts that have small, medium, large, XL, 2XL, 3XL, 4XL and each size has its own price. My problem I am dealing with is that not all designs are available in each size. How do I set this up to pull each size & price but also notify the user if the size isn't available?

This calls for a separate table in which sizes and prices are related to shirts (shirt id).

inventory table
id
shirt_id
size
price
quantity

You will want to search on the net for references on database design.

artisticre

this is what I was thinking. But if I do it this way, the user has to have an entry in the database for each size like below.

Animals Small $5.00
Animals Medium $6.00
Animals Large $7.00

and so forth. doesn't this create a lot of redundancy?

ultraslacker

Redundancy has a special meaning in database design. In your example, the repeated columns animals and size are not redundant.