Denormalize SQL Tables?

[monky2k6]

Hallo,

I hope you can help me with some SQL.

My problem:
I've got 4 Tables.
1. Articles
2. Article_Attributes (a.k.a: "What Options does ist have?")
3. Possible_Options
4. Possible_Option_Values

Ok, what I got here is an simple Web-Shop with variations of articles.

For example:
Table "Articles":

	ID	Name
	1	T-Shirt

In the Attributes:

	ArticleID	OptionsID	OptionValueID
	1		1		1
	1		1		2
	1		2		1
	1		2		2

In the Table Options:

	OptionsID	Name
	1		Color
	2		Size

And in the Table Option_Values:

	ID	OptionID	ValueName
	1	1		Red
	2	1		Blue
	1	2		Small
	2	2		Large

So, what I now need is some Output like:

	Name		Variation
	T-Shirt		Red - Small
	T-Shirt		Red - Large
	T-Shirt		Blue - Small
	T-Shirt		Blue - Large

Is this possible? I think it's something like... a "denormalization".
It would be nice if this is possible in SQL, but it could be handled in PHP, too.

Can somebody please help me?

[Shadaez]

tables don't work on FP any more, it would be best to use code tags and do some whitespace formatting

[DrTaxi]

If you only ever had two Options for each Article, you could do a selfjoin of Option_Values:

SELECT Articles.Name, CONCAT(ov1.ValueName, ' - ', ov2.ValueName) as 'Variation'
FROM Articles,
	Attributes as a1,
	Attributes as a2,
	Option_Values as ov1,
	Option_Values as ov2
WHERE a1.ArticleID = Articles.ID
		AND a2.ArticleID = Articles.ID
		AND ov1.OptionID = a1.OptionsID
		AND ov1.ID = a1.OptionValueID
		AND ov2.OptionID = a2.OptionsID
		AND ov2.ID = a2.OptionValueID
		AND a1.OptionsID != a2.OptionsID
		AND a2.OptionsID < a1.OptionsID

As soon as you have more, though, this breaks.

Instead, you should make a new table, Items:

	ID		ArticleID
	1		1
	2		1
	3		1
	4		1

And a table for the specific attributes an item has, ItemOptions: (Primary key over ItemID and OptionID, of course)

	ItemID	OptionID	OptionValueID
	1		1		1
	1		2		1
	2		1		2
	2		2		1
	3		1		1
	3		2		2
	4		1		2
	4		2		2

Then you can do this:

SELECT Articles.Name, GROUP_CONCAT(Option_Values.ValueName SEPARATOR ' - ') as 'Variation'
FROM Articles,
	Items,
	ItemOptions,
	Option_Values
WHERE Items.ArticleID = Articles.ID
	AND Items.ID = ItemOptions.ItemID
	AND Option_Values.OptionID = ItemOptions.OptionID
	AND Option_Values.ID = ItemOptions.OptionValueID
GROUP BY Items.ID

And it'll work no matter how many options per article you have.
This schema is also much more realistic. You can exclude specific variations (maybe you only sell small blue, small red and large blue shirts, but not large red ones) and have separate stock per variation if you add a column for that to Items.
If you do want all variations, and no extra stock etc., it might be possible to create Items and ItemOptions as views or subqueries with all option permutations, but I don't know enough SQL for that.

By the way:
The table names aren't very descriptive (especially with my additions), you might want to try finding better ones.
In one table you write OptionsID for what should really be called OptionID.
Option_Values being an extra table is unnecessary. For each attribute, there's only ever going to be one Option_Value (as identified by a combination of OptionID and OptionValueID, which must be unique), so you can merge those: Attributes(ID int primary key, ArticleID int, OptionID int, OptionValue varchar/text). Of course, if you need multiple names for one value (e.g. if you have different languages), you need two tables. Even then, that split primary key you use in Option_Values (combination of ID and OptionID), while totally allowed, is confusing and prevents you from using AUTO_INCREMENT - just use unique IDs independent of OptionID.

The Items/ItemOptions example above is a very common construct in database design. I suggest reading up on entity-relationship modelling and how to transform an entity-relationship model into a relational model (the latter being what SQL databases use, so you can literally translate that into SQL). It's boring theory and you already do a lot of it subconsciously. But knowing that your Article is an entity that has many Items, which each belong to exactly one Article etc.etc., knowing how to draw a diagram of that if you need to (and drawing diagrams does help you get less confused), and how to turn that into tables is actually pretty useful.