Before I ask my question, let me describe the situation. I'm
programming a product directory, where products belong to a certain
category (or multiple categories.)

Currently, all products, no matter what category they are in, have the
same type and amount of attributes - for example name, description and
price. I wish to make it so that products contain attributes depending
on what category they are in - many products will have attributes in
common - such as a name and description - then there will be some
attributes not in common, such as books having a "hardback or
paperback" attribute which would not be applicable to the DVDs
category.

Attributes can be any value - boolean, numeric, text etc.

Also, a product can belong to multiple categories, and it would have
attributes from all of those categories it belongs to. But it would
only have attributes of the categories it is in - not inherit any from
grandparents i.e. the parent category of its category(s).

As an example, for a "mobile devices" catalog:

available attributes:

1 name
2 description
3 GSM frequencies 800, 1800, 1900 (a set)
4 audio formats MP3, WMA (a set)

categories:

1 phones
2 audio players


Products in category 1 have attributes 1, 2 and 3.

Products in category 2 have attributes 1, 2 and 4.

Products belonging to both categories 1 and 2 (say a phone that plays
audio files) would have all attributes 1-4.

In the following paragraphs, when I say add "edit" something - I mean
either adding that item to the db, or modifying or deleting it.

I want to be able to edit products as expediently as possible, and I
don't want to have to choose what attributes to select for a product
every time I edit a product. In other words, whenever I edit a product,
after choosing what category(s) it belongs to, I am immediately
presented with all the attributes I need to fill in for that product -
attributes are attached to a category rather than a product.

I envisage a number of editor screens:

The "attribute editor" screen is where I edit the attributes that are
potentially available to all categories.

The "category editor" screen is where I would edit category attributes
- it would display something like checkboxes allowing me to choose what
attributes go into this category. On this screen I would also choose
the parent category of the category.

The "products editor" screen is where I edit products - where I choose
a category(s) for a product, and fill in the values of attributes for
that product.

Currently attributes (such as name & description) are embedded in the
product detail rows, but I want to break the attributes out of there.

My question is what would be my data schema for attaching attributes to
categories (and thus their products) in an efficient, normalized way?

Should I attach attributes to products with a products_attributes table
containing rows such as:

id_product / id_attr / id_value

or should I attach attributes to categories - with an attributes table
along with a category_attributes table, such as:

table_categories: id / id_parent / name

table_attributes: id / name

table_category_attributes: id / id_category / id_attribute

But what about those attributes common to all categories such as name
and description?

This is where I start to get lost when wondering how to store the
actual data for each product in the db, and how to present it for
editing to the user when they add/edit a product (i.e. to know what
attributes available.)

I want no repeated data, so even if a product appears in two
categories, its name etc. is only stored once in the db.

Any advice would be appreciated!

Miner2049er

Oh God, Where to begin??

Then each one will need its own table in an RDBMS. This basic. They
are logically different entities,, so they get separate tables.

Never think about this when you are designing a database -- this is
programming and not DB design. Wrong mindset.

This design flaw is called EAV or "Entity-Attribute-Value"; It ius
sooooo common and soooo damn bad that it has a name, like many
diseases.

And where you get the idea that everything has a magical "id" column??

CREATE TABLE ProductCategories
(product_id CHAR(13) NOT NULL
REFERENCES Products (product_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
category_nbr INTEGER NOT NULL
CHECK (category_nbr BETWEEN 100 AND 999),
PRIMARY KEY (product_id, category_nbr));

Thanks for the reply.

This doesn't sound right for either products OR categories - starting a
new table for each instance?

Yep, I know it's not always needed.

Does anybody have any comments on the following schema (with demo
values.) At first glance it seems to be OK, but I'm not sure if there
are any hidden gotchas...


table_category_products

id_category - id_product
----------- - ----------
1 - 1
2 - 2
1 - 3


table_attributes

id - name
-- - ----
1 - title
2 - condition
3 - mileage
4 - covertype


table_categories

id - name (parent category info also in this table)
-- - ----
1 - books
2 - cars


table_category_attributes

id_category - id_attribute
------ - -------
1 - 1
1 - 2
1 - 4
2 - 1
2 - 2
2 - 3


table_product_attributes

id_product - id_attribute - value
---------- - ------------ - -----

1 - 1 - honda civic
1 - 2 - falling apart
1 - 3 - 500,000
2 - 1 - lord of the rings
2 - 2 - almost new
2 - 4 - hardback
3 - 1 - fire in the valley
3 - 2 - slightly used
3 - 4 - paperback


Thanks in advance for any replies.

Miner2049er.

Why do you put "id_" in first and then qualify it? Ptroducts have an
identifier,. not the other way around. Ever see ISO-11179 rules? God
did not put a magic number of the bottom of everything in creation.

No, in RDBMS, an instance is modeled by a row in a table. One [roduct,
one row.

Your EAV design cannot be maintained, cannot take constraints, cannot
be summariezed, cannot be validated, take an order of magnitude more
time and space to do the simplest operations, etc. They fall apart in
about one year of real usage.

I found an old "cut & paste". Som3eone like you posted this:

CREATE TABLE EAV -- no key declared
(key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL);

INSERT INTO EAV VALUES ('LOCATION','Bedroom');
INSERT INTO EAV VALUES ('LOCATION','Dining Room');
INSERT INTO EAV VALUES ('LOCATION','Bathroom');
INSERT INTO EAV VALUES ('LOCATION','courtyard');
INSERT INTO EAV VALUES ('EVENT','verbal aggression');
INSERT INTO EAV VALUES ('EVENT','peer');
INSERT INTO EAV VALUES ('EVENT','bad behavior');
INSERT INTO EAV VALUES ('EVENT','other');

CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
(id INTEGER IDENTITY (1,1) NOT NULL,
bts_id INTEGER NULL,
key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL );

INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');

Ideally, the result set of the query would be Location Event count
(headings if possible)

Bedroom verbal aggression 1
Bedroom peer 0
Bedroom bad behavior 0
Bedroom other 2
Dining Room verbal aggression 0
Dining Room peer 0
Dining Room bad behavior 0
Dining Room other 0
Bathroom verbal aggression 0
Bathroom peer 0
Bathroom bad behavior 0
Bathroom other 0
courtyard verbal aggression 0
courtyard peer 1
courtyard bad behavior 0
courtyard other 1

Also, if possible, another query would return this result set. (I think
I know how to do this one.)

Location Event count
Bedroom verbal aggression 1
Bedroom other 2
courtyard peer 1
courtyard other 1

Here is a From: Thomas Coleman

SELECT Locations.locationvalue, Events.eventvalue,
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue

FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event'
) AS EventData
ON LocationData.bts_id = EventData.bts_id
) AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events
ORDER BY Locations.locationvalue, Events.eventvalue ,
SELECT Locations.locationvalue, Events.eventvalue
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue

FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event') AS EventData
ON LocationData.bts_id = EventData.bts_id)
AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue)
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events;

Is the same thing in a proper schema as:

SELECT L.locationvalue, E.eventvalue, COUNT(*)
FROM Locations AS L, Events AS E
WHERE L.btd_id = E.btd_id
GROUP BY L.locationvalue, E.eventvalue;

The reason that I had to use so many subqueries is that those entities
are all lopped into the same table. There should be separate tables for
Locations and Events.

The column names are seriously painful. Beyond the fact that I
personally hate underscores in column names, using underscores at the
end of the column name is really non-intuitive. I removed them for my
example and came across the next column name faux pas. Don't use "key"
and "value" for column names. It means that the developer *has*
surround the column name with square brackets for everything which is a
serious pain.

There is such a thing as "too" generic. There has to be some structure
or everything becomes nothing more than a couple of tables called
"things". The real key (no pun intended) is commonality. Is there a
pattern to the data that they want to store? It may not be possible to
create one structure to rule them all and in the darkness bind them.

"To be is to be something in particular; to be nothing in particular is
to be nothing." --Aristole

All data integrity is destroyed. Any typo becomes a new attribute or
entity. Entities are found missing attributes, so all the reports are
wrong.

ry to write a single CHECK() constraint that works for all the
attributes of those 30+ entities your users created because you were
too dumb or too lazy to do your job. It can be done! You need a case
expression almost 70 WHEN clauses for a simple invoice and order system
when I tried it as an exercise.

ry to write a single DEFAULT clause for 30+ entities crammed into one
column. Impossible!

Try to set up DRI actions among the entities. If you thought the WHEN
clauses in the single CASE expression were unmaintainable, wait until
you see the "TRIGGERs from Hell" -- Too bad that they might not fit
into older SQL Server which had some size limits. Now maintain it.

For those who are interested, there are couple of links to articles I
found on the net:

Generic Design of Web-Based Clinical Databases
http://www.jmir.org/2003/4/e27*/

The EAV/CR Model of Data Representation
http://ycmi.med.yale.edu/nadka*rni/eav_CR_contents.htm

An Introduction to Entity-Attribute-Value Design for Generic
Clinical Study Data Management Systems
http://ycmi.med.yale.edu/nadka*rni/I...20*systems.htm


Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
Database
http://www.pubmedcentral.nih.g*ov/ar...=pub*med&pubme...


Exploring Performance Issues for a Clinical Database Organized Using
an Entity-Attribute-Value Representation
http://www.pubmedcentral.nih.g*ov/ar...=pub*med&pubme...

--CELKO-- wrote:

Hi Joe

Are they? I don't think they are completely different. Isn't it more like they are all
products? So a book is a descendant of a product.
So why don't we use a table for products with all the common attributes and additional
tables for all the additional attributes a book has.
Isn't this more the solution that follows from an according ER?

I agree.

I agree again. I can't say I don't like this EAV tables, its more that I hate them

Regards
Stefan

and additional tables for all the additional attributes a book has.
Isn't this more the solution that follows from an according ER? <<

This gets messy, too.The classic scenario calls for a root class with
all the common attributes and then specialized sub-classes under it.
As an example, let's take the class of Vehicles and find an industry
standard identifier (VIN), and add two mutually exclusive sub-classes,
Sport utility vehicles and sedans ('SUV', 'SED').

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

Notice the overlapping candidate keys for performance and searching. I
then use a compound candidate key (vin, vehicle_type) and a constraint
in each sub-class table to assure that the vehicle_type is locked and
agrees with the Vehicles table. Add some DRI actions and you are done:

CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

I can continue to build a hierarchy like this. For example, if I had a
Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.

If an entity doesn't have to be exclusively one subtype, you play with
the root of the class hierarchy:

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);

Now start hiding all this stuff in VIEWs immediately and add an INSTEAD
OF trigger to those VIEWs.

--CELKO-- wrote:
Well it seems I don't really understand your idea but won't get this messy too? You would
have to change the root table everytime you add new car types?


I wanted to create more something like that:

product (id (PK), common attributes)

book (id (PK), product_id (FK), additional attributes (like author, pages, isbn, ...)

dvd (id (PK), produckt_id (FK), additional attributes (length, dvd-count, ...)

....

So I don't see where this messes up? Just one table for all products and one table for
each special type of product?

Maybe I'm thinking to much in my ER model to realationship model transformation?!


Regards
Stefan

Thanks for all the replies so far folks. Still working on it...

I also talk more about this topic in a new thread "Craigslist data
schema" at:

http://groups-beta.google.com/group/...42e1b318d65639

Miner2049er.

Let's take a look at the "non-DDL" for books:

book (id (PK), product_id (FK), additional attributes (like author,
pages, isbn, ...);

ISBN is the industry standard identifier for a book, but you make it an
atribute - which can be changed without changing they key. Opps!!
That isn't right, is it? You then create a magically "id" out of
nowhere instead. To be an identifier, it must identify something in
particular, like the ISBN does. Surely you know better than to fake a
record number with IDENTITY, don't you??

Common attributes might be weight, sku, price. Factoring them out to
fake an OO class strucfures does not save yoiu anything in the way of
storage and you have to ALWAYS do joins to get the basic data back. It
just makes OO people feel better.

If you need to assure that two different items are not given the same
SKU, you can set up a CREATE ASSERTION statement in SQL-92 that is a
bit harder in T-SQL.

If you need to get the common data (i.e. "How many tons of inventory do
we have?") then do a UNION ALL on each table.