From basic relational database design, we know that a multivalued attribute is supposed to have its own table, where the primary key to this table is the multi-valued attribute along with the key from the originating table. This is the general rule. And it makes perfect sense, ESPECIALLY when that multi-valued attribute will have a lot of values and you have no idea as to the max. amount.

Here's my situation. For my products table, I have a "price" attribute. The pricing for any given product may have two to six different prices depending on the quantity. And each new price has a respective quantity break that goes with it.

So in an effort to follow proper database design techniques, I created a separate product_pricing table shown below:

CREATE TABLE products_pricing (
	item_num VARCHAR(20) NOT NULL,
	quantity INT,
	price DECIMAL(6,2),
	code CHAR(1),
	PRIMARY KEY (item_num, price, code, quantity),
	INDEX (item_num),
	FOREIGN KEY (item_num) REFERENCES products (item_num)
	ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;

And this is fine, but really, because i KNOW, in advance, that the MAXIMUM number of price breaks, and their respective quantity breaks, will be SIX, would it be aweful of me to just make those as attributes in the main products table? :o And then, for a given product that only had three prices, the quantity and the price of the other columns would just be NULL (/N).

So in that case, in the main table, I would have price1, price2, ..., price 6, quantity1, ..., quantity6, and maybe cost1, cost2, ..., cost6.

Someone say this is fine so I can do it and say "HE told me it's okay!". :p

    I suggest you do it the hard way.

    Reason?

    What happens when the seventh and eigth price points come? Things always change, so plan in advance for them. More work now, less work then...

    (sorry. not what you wanted to hear, but I believe the practical way to do it...)

      Write a Reply...