Re: [tlug] database design idea... what do you think?

["Dimitar Dimitrov" <dimitar.dimitrov@example.com>]

On 2008-01-11 14:38 +0900 (Fri), Micheal Cooper wrote:

> What do you think of the following idea for a way to introduce ad-hoc
> attributes for users:
>
> Make table "attributes" in a many-to-many relationship with table
> students.

Such a design can work, but practice shows that more often than not , with such schemas, as the application grows one starts keeping a separate table of metadata (what types of attributes each entity may have, etc.) Once you have a second application accessing the same data, you have to take care that you keep your metadata in sync and it becomes a PITA. In the end when you decide to demise the application and migrate the data to the succeeding system, you are inevitably going to run in data quality issues caused by the lack of RI constraints.

No matter where your metadata lives -- be it in the DBMS, in your code or on a piece of paper, you still have to maintain it. Puting it close to the data lets you use the database facilities which makes your job easier. 

By using EAV tables instead of creating normal columns for each attribute, you are bypassing big part of what the DB does for you (also you can't use most of the tools working using schema metadata like sqsh, ORMs or RoR-style frameworks.) If you are still thinking that your application is not going to grow complex and you don't need to maintain the data in the long run, you might be better served by a record-store like BerkeleyDB instead of a relational database.
 
my 2円,
Dimitar