How to store meta-data on columns

Question

Let's say you're collecting insider info on upcoming superhero movie releases and your main Movie table looks something like this:

Table 1

Title              Director   Leading Male      Leading Female    Villain
--------------------------------------------------------------------------
Green Lantern      Kubrick    Robert Redford     Miley Cyrus     Hugh Grant  
The Tick          Mel Gibson  Kevin Sorbo        Linda Hunt    Anthony Hopkins

This should work very well in general and allow very easy queries as well as comparisons between rows.

However, you'd like to track the source of each data fact, as well as the name of the journalist who discovered the fact. This seems to suggest some sort of an EAV table like this:

Table 2

Movie             Attribute            Value          Source          Journalist
----------------------------------------------------------------------------------
Green Lantern      Director           Kubrick         CHUD              Sarah
Green Lantern    Leading Male      Robert Redford     CHUD              James
Green Lantern   Leading Female      Miley Cyrus    Dark Horizons        James
Green Lantern      Villain           Hugh Grant       CHUD              Sarah
The Tick           Director          Mel Gibson       Yahoo            Cameron
...

Which, while it easily captures the meta-data that we wanted, makes queries harder. It takes a bit more to simply get all the basic data of a single movie. More specifically, you have to deal with four rows here to get the four important tidbits of information on the Green Lantern while in table 1 it is a single, nicely encapsulated row.

So my question is, in light of the complications I just described, and because I know in general EAV tables are to be avoided, is the EAV still the best solution? It does seems like it is the only reasonable way to represent this data. The only other alternative I see is to use table 1 in conjunction with another one that only houses meta data like this:

Table 3

Movie             Attribute            Source          Journalist
----------------------------------------------------------------------------------
Green Lantern      Director             CHUD              Sarah
Green Lantern    Leading Male           CHUD              James
Green Lantern   Leading Female      Dark Horizons         James
Green Lantern      Villain              CHUD              Sarah
The Tick           Director             Yahoo            Cameron
...

But this is very dangerous because if someone changes a column name in table 1, like "Villain" to "Primary Villain," the row in table 3 will still simply say "Villain" and thus the related data will be unfortunately decoupled. This could be helped if the "attribute" column was linked to another table that served as an enumeration of the columns of table 1. Of course, the DBA would be responsible for maintaining this enumeration table to match the actual columns of table 1. And it might actually be possible to improve this even further by instead of creating the enumeration table by hand, use a system view in SQL Server that houses the names of the columns in table 1. Though I'm not sure you can have relationships that involve system views.

What do you suggest? Is the EAV the only way to go?

And what if it was only one meta-data column (just "Source" without "Journalist") - is it still necessary to go the EAV route? You could have columns "Director," "Director_Source," "Leading Male," "Leading Male_Source," etc., but that gets ugly very quickly. Is there some better solution I'm not thinking of?

If I haven't clarified any point please comment and I'll add more as necessary. Oh yeah, and the movie data I used is fabricated :)

Edit: To restate my primary question concisely, I would like to have the simplicity and the true RDBMS design of table 1, which really describes a movie entry well, while still storing the meta data on the attributes in a safe and accessible manner. Is this possible? Or is EAV the only way?

Edit 2: After doing some more web research, I have yet to find a discussion on EAV's that centered around the desire to store metadata on the columns. The primary reason given to implement an EAV is almost always dynamic and unpredictable columns, which is not the case in my example. In my example, There are always the same four columns: director, leading male, leading female, villain. However, I want to store certain facts (source and journalist) about each column for each row. An EAV would facilitate this, but I would like to avoid resorting to that.

Update

Using the Table 2 design except for renaming the column "Movie" to "Name" and calling the whole table "Movie," here is the pivot operation in SQL Server 2008 to get back Table 1:

SELECT Name, [Director], [Leading Male], [Leading Female], [Villain]
FROM (Select Name, Attribute, Value FROM Movie) as src
PIVOT
(
Max(Value)
FOR Attribute IN ([Director], [Leading Male], [Leading Female], [Villain])
)  AS PivotTable

Answer 1

Here's another idea...feel free to punch holes in it :)

Table: Movie
Columns: MovieId|Movie|Director|LeadMale|LeadFemale|Villain

Table: MovieSource
Columns: MovieSourceId|MovieId|MovieRoleId|Source|Journalist

Table: MovieRole
Columns: MovieRoleId|MovieRole
Values: 1|Director, 2|LeadMale, 3|LeadFemale, 4|Villain

What I'm thinking is that the columns in the movie table could be of different types (in your example, they are all strings/varchars, but they could be, say, numerical or date information that also has a source).

The column types for the source data, however, probably wouldn't vary as a function of the column types of the movie data, so you could use more of an EAV system for the source without losing the integrity of your data.

The MovieRole table allows you to explicitly enumerate the roles so you can create a sure linkage between the source and a given cell of the movie table.

-Dan

Answer 2

Seeing as you only have the two fields for source data (Source and Journalist), I would recommend a meta-data table like this:

Movie    DirectorSource  DirectorJournalist  LeadingMaleSource  LeadingMaleJournalist ...
---------------------------------------------------------------------------------------
The Tick   Yahoo           Cameron           ...                ...

This will keep the less important source data out of the main table, but the queries will not get complicated and your code will be more readable.

I would only advise EAV if ...

• You have more than 3 fields of source meta-data
• You need to be able to add or change movie fields easily. (changes like 'Villain' to 'Primary Villain' are being done several times per day)

Answer 3

Hmm.... I've not used this, so I'm not speaking from experience (i.e. don't blame me if it doesn't work), but on the surface it seems that you could store "common" data that you know will always be there as you would in a normal table, and "metadata" that might change as XML. The question then is how to query it nicely, and I think you might be able to do that as described HERE.

Answer 4

I would make my decision based on what I need to code.

If src/journo is simply additional info, I would go for further columns. But if I know I'm going to end up building complicated src/journo queries, I would go EAV, as it'll be easier to search for a journalist's references down the meta table than having to go into LeadingFemaleJournalist and VillainJournalist etc.

Personally - I would be inclined dump the src/journo meta-data into another table EAV-style, but use a FK to define an Attribute definition table. Having a freeform Attribute text field is a recipe for disaster - always control your attributes through a constraint. Triggers could be implemented to improve referential integrity if required.

For me, it comes down to point-of-view. Do you see sources and journalists being relational concerns in their own right or are they just additional pieces of data to complement a Movie? The next level of refinement would be to create different tables for MovieDataSource and MovieDataJournalist which could allow you to map FKs to a tables defining valid Sources and Journalists (and further information on those Sources/Journalists could then be fleshed out). What you will have done here is to establish a many-to-many relationship between the Movie entity and the Source (and also Journalist) entity.

Answer 5

My response may seem a bit too philosophical for SO. Bear with me.

I think that the "Source" column isn't subject matter data, but rather meta-data. It's really data about how we come to know some other bit of data. That makes it data about data, and that's meta-data.

Among the reasons why EAV causes the problems that it does is the fact that it intermixes data and metadata in a single row. There are times when I've deliberately done that myself, as an intermediate step towards a result I want to acheive. But I've tried never to intermix data and metadata in my deliverables.

I know why I never did that, but I can't explain it concisely.

Answer 6

Since no one else is really taking a crack at it, I'm going to answer my own question. I'm pretty sure an EAV-like table is indeed the only way to go. To store metadata on each column (regarding the source and journalist in this case), you're really treating each column as an entity in itself, which is what an EAV allows.

You could go other routes, like adding a second and third column for each original column to store data, but that is definitely breaking some fundamental normalization rules and will probably only cause you pain later.