schema for storing different varchar fields over time?

Posted by Henry on Stack Overflow See other posts from Stack Overflow or by Henry
Published on 2010-05-14T19:59:51Z Indexed on 2010/05/14 20:04 UTC
Read the original article Hit count: 296

This app I'm working on needs to store some meta data fields about an entity. The problem is that we can already foresee that these fields are going to change a lot in the future. I'm using Hibernate (in ColdFusion) and each entity's properties are translated to one column in the entity table, but altering table columns later down the raod will be costly and error-prone right?

Should I go for something like this?

MetaDataField
-----
metaDataFieldID (PK), name

FieldValue
----------
EntityID (PK, FK), metaDataFieldID (PK, FK), value [varchar(255)]

Is this common? Anything to watch out for?

p.s. I also thought of using XML on SQL Server 05+. After talking to some ppl, seems like it is not a viable solution 'cause it will be too slow for doing certain query for reporting purposes.

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about schema