Im trying to design a database to hold metadata for video, image and
audio clips.

The current prototype (read quick dirty hack) has two tables:

tblMedia
id,metaid

tblMeta
id,fieldname,value

This lets any given metaid have multiple pieces of metadata associated
with it. The value field is meant to hold any data type, from boolean
values to large amounts of text.

To normalise this database I decided that I need to separate the
tables as follows:

tblMedia
id,metaid

This table contains a list of media items and the associated metadata
id. There could be multiple pieces of metadata per media item.


tblMediaMetaRef
metaid,datatypeid

This table lists the metaids and what datatype each metadata is.


tblMetaDataTypes
id,type

This table lists the different type of datatype we want to store.

My idea would be to then have a table for each type of metadata data
type that I wanted to store.

My concern is firstly how sensible is this design? Secondly is there a
better way of doing it? Finally if I am on the right track how can I
pull the results from the server in a single SQL query as this design
would require a select statement for each piece of meta?

I would appreciate any help on this one

Cheers,
Tom

Tom Shaw wrote:
This is not a normalized design. It's an example of a design called
"Entity-Attribute-Value". This design has a number of problems. Read
back through the archives of this newsgroup; it has been discussed many
times.

One example: how would you declare a certain attribute as mandatory in
this design? That is, for a given row in tblMedia, there _must_ be a
matching row present for a certain fieldName in tblMeta. The equivalent
of declaring a conventional column as NOT NULL. Answer: you can't do
this with EAV. There's no declarative way in SQL to say that a row must
exist with certain values, and to automatically throw an error if the
value is not present.

This is one of several ways the EAV design breaks the relational model.

This is closer to being in normal form. But you need to make sure you
have separated attributes based on the logical attribute -- not only on
the physical SQL datatype (e.g. integer, varchar, etc.).

For instance, suppose this were an email message storage database. You
have a message, which can have many Recipients, and many Attachments.
You decide to store both Recipient values and Attachment values as a
text blob. Does this mean that they can go in the same table? No --
because you should not store values that have different purposes in the
same column.

More sensible than storing all the dependent attributes in a single EAV
table.

Right, this more or less requires that you do a separate SQL query for
each logical type of meta. Otherwise you get into problems of
cross-products. If you have 4 Recipients and 3 Attachments, the result
of the query against both will have 12 rows. Then if you have 5 of some
other type of dependent data, you will have 60 rows.

Why do you need to get all the dependent data in a single query?
Queries are not so expensive. You should live it up a little!

On the other hand, the EAV design actually does have an advantage here.
You can get all the data in a single query, with a result set 4+3+5
rows long. But you lose a lot of the other features that an RDBMS can
give you.

Some people will tell you that EAV is always pure evil. But I say go
ahead and use it, if it solves you needs -- as long as you understand
what you are giving up by doing so.

Regards,
Bill Karwin

EAV-type designs in RMDBs have advantages and disadvantages. In many
cases, they become unpractical due to reduced SQL support / performance
and increased coding/query complexity. You may be interested to read
the closely related thread titled "Generalised Approach To Storing
Address Details" in comp.databases.theory.

See www.dbfordummies.com/example/ex111.asp for an example that stores
varied data for Multi-Medias using dbd.

On Dec 19, 1:18 am, "Neo" <neo55... (AT) hotmail (DOT) com> wrote:
The problem with EAV is not "SQL support". The problem is more
fundamental than that. Bill covered it well, so I won't repeat it. EAV
is just a poor choice.

To Tom,
Do you really want to have NO limits on what information can be
attached to "theMedia" entity? Don't you really have some required
information and some optional? Think about it.

Ed

We do not use SQL for metadata. You are completely missing the point
of RDBMS and re-discoverying the EAV design mistake. Newbies do this
about one a month in this newsgroup.

You also screwed up the data element names and need to look at
ISO-11179 Metadata standards. The "tbl-" prefix is just silly as well
as redundant. Columns are not anything like fields. There is no such
thing as a "magical Universal" identifier; it has to be something in
particular. Ditto for the equally vague "value" thing

How did you plan to get any meaningful informaton from "Britney Spears,
Squids and Automobiles" -- a descriptive name of your magical
meta-table.

Oh, please write a CHECK() constraint to enforce data integrity.

"data_type_id" Is a true disaster. A data element can be a type or an
identifier, but NEVER both by definition.

Classic design error, but you did not even do a good job of making the
error.

There is amost no worse way I know of. Go with a solid relational
design; one table for each kind of entity, one table for each
relationship; auxiliary tables for support. Meaningful, precise data
element names, etc.

That is one of many, many reason we never use EAV. LI found an old
"cut & paste". Someone 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 an answer 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 plopped into the same table. There should be separate tables
for Locations and Events.

The column names are seriously painful. Don't use reserved words like
"key" and "value" for column names. It means that the developer *has*
surround the column name with double quotes for everything. And they
are too vague to be data element names anyway!

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.

Try 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.

Try 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...

Note, encoding data as EAVs by itself is fine. It works well in a db
like dbd and non-dbs such as Prolog. EAVs are closely related to the
well-founded FOL. RM/RMDBs can't manage complex EAVs in a systematic
manner. Try replicating the example below.

Yes, Bill covered it well. In some cases where one needs to manage
varied data structures including those not known at design-time, one
may benefit by utilizing some level of EAV-type design. If you have a
non-EAV schema for OP's requirements, please post it.

Below dbd example represent that john likes mary, john hates bob, and
like is opposite of hate. Then a query finds the person with whom
john's relationship is opposite that of with mary. For fun, try to post
and equivalent RMDB solution that also follows good practices (ie
systematic, doesn't violate Information Principle, NULL-less,
normalized, non-redundant and remains so when the example is extended
to include things unknown at design-time):

(new 'john)
(new 'mary)
(new 'bob)

(new 'like)
(new 'hate)
(new 'opposite)

(set like opposite hate)
(set hate opposite like)

(set john like mary)
(set john hate bob)

(; Get person with whom
john's relationship is opposite of that with mary)
(; Gets bob)
(get john (get (get john * mary) opposite *) *)

(; Get person with whom
john's relationship is opposite of that with bob)
(; Gets mary)
(get john (get (get john * bob) opposite *) *)

The following dbd example is approximately equivalent to the RMDB
example further below.

(; Locations)
(new 'bedroom 'location)
(new 'dining_room 'location)
(new 'bathroom 'location)
(new 'courtyard 'location)

(; Events)
(new 'verbal_aggression 'event)
(new 'peer 'event)
(new 'bad_behavior 'event)
(new 'other 'event)

(; Incident1)
(new 'incident1 'incident)
(set (it) location bedroom)
(set (it) event other)
(set (it) event bad_behavior)

(; Incident2)
(new 'incident2 'incident)
(set (it) location bedroom)
(set (it) event other)
(set (it) event verbal_aggression)

(; Incident3)
(new 'incident3 'incident)
(set (it) location courtyard)
(set (it) event other)
(set (it) event peer)

(; Get count of indicents in location bedroom with event other)
(; Gets 2)
(count (and (get incident instance *)
(get * location bedroom)
(get * event other)))

(; Get count of indicents in location courtyard with event peer)
(; Gets 1)
(count (and (get incident instance *)
(get * location courtyard)
(get * event peer)))

Neo wrote:
That just does not follow. The disadvantages are enormous,
well-attested and plain to every systems integrator or BI / MIS
professional who knows and loathes that so-called "model".

Naturally that would be a schema that modelled the various "metadata"
attributes of interest. Since the OP didn't specify them there is no
point attempting an answer.

--
David Portas

David Portas wrote:
You're right, the disadvantages of EAV are huge. Avoid this design if
at all possible. You basically throw out the window many of the
benefits of SQL as a declarative language.
- No way to do NOT NULL on an attribute
- No way to do referential integrity or lookup tables for attributes
- No way to enforce data typing (e.g. a given attribute must be a DATE)
- Expensive to query which attributes for an entity have values
- Expensive to produce human-readable result sets
- Storing attribute names as values causes a painful itching sensation

My point is that if you understand all these disadvantages and you
accept them consciously, using EAV isn't the end of the world.

Sometimes it can be necessary, for instance:
- Attributes aren't known at design time
- Attributes may be introduced at any later time
- Attributes vary widely per entity

Sometimes you are given an assignment to create a schema that is
"extensible" (may store new attributes), but you are required to account
for any future extension with current code and schema. That is, you are
prohibited from altering code and schema in the future to account for
new attributes.

As far as I know, the only way to implement a system under these
conditions is with an EAV design. If you have another solution for this
scenario, that complies with rules of normalization, by all means please
share it with us.

Regards,
Bill K.

Neo wrote:
As you point out Prolog is not a database. (Last I checked it was a
programming language. Why should you bring it up? It's like comparing
apples to race cars.)


I don't lurk in comp.databases.theory, so I have no idea offhand what
you mena by FOL.
EAVs are a poor choice for almost any data storage system. Related data
is spread who-knows-where throughout the storage system. That's why
there are guidelines (normalization rules) for structuring data in a
RDBMS. EAV breaks the fundamental concept: attributes for an entity
should all be together.

Why do you challenge this way? I have no intention of playing your
games.

ONE: I do not know what attributes he desires.
TWO: before issuing such a challenge, why don't you post one that fits
his needs. (you can't because of ONE, but I know you'll keep posting
your obscure examples anyway.)

[silly example deleted]

Ed