Full site membership is required for this article. Already a full member? Login here. You currently have a guest membership. You can upgrade to full site membership here (free trial available). Database Design Using Key-Value Tables - Part 2 – Model Benefits and Drawbacks   Article Abstract: In the following article we are going to cover the use of key-value tables in a database design. An alternative to the traditional RDBMS design is a use of key-value tables; a key-value table is a table holding data in “vertical” manner (vs. traditional design which holds data in “horizontal” manner).   Read this article...

Related Articles - For Members. All Articles By Author Backing up and restoring databases and transaction logs Backup and Restore – Back to Basics with SQL LiteSpeed Automate Your Database Creation Understanding SQL Server roles Standard SQL Server Databases - What Are They? Oracle 2-Day DBA in x Days Post 3; Creating a database RMAN 8I & 9I in Nutshell Attaching and Detaching Databases Second Normal Form (2NF)

RE: Database Design Using Key-Value Tables - Part 2 – Model Benefits and Drawbacks
by Ben Abshire (posted: 2/3/2006 10:23:24 AM)
During one of my most recent database designs, the idea of using a key-value pair in the database was something I strongly considered. I needed flexibility due to the nature of the data I was receiving into my database. Without boring you with details, let me tell you, I had anything and everything under the sun to try and fit into a standard relational data model. But using a key-value pair system has many limitations and ultimately is difficult to use. Instead, I took advantage of Xml and the new Xml capabilities in SQL Server 2005 (I also did this in SQL Server 2000). I was able to create an extremely flexible “data structure” without having to use a key-value pair system because of the inherent abilities of Xml. I would highly recommend using Xml instead of key-value pairs. With a little forethought, it can give you all the benefits of a key-value pair system but allow you to maintain a much more structured or traditional relational model.

RE: Database Design Using Key-Value Tables - Part 2 – Model Benefits and Drawbacks
by Benno Savioli (posted: 2/6/2006 9:14:17 AM)
Thanks for the interesting topic - I'm working too - but only for three purposes with similiar structures (but using MS ACCESS 2000).
a) for electronic questionnaires that usually change from interrogation to interrogation and where I'm simply importing the data in a quick way with no need to edit and/or maintain the data over some period
b) for the import of standardised statistical time-series data related to an object (e.g. a set of different labour inspectorates etc.) and
c) as a way to expand my database to some flexibility in additional data without having to adopt the database structure in too short intervals. In this case the challenge is the set-up of user forms and the integration in reporting

In a different environment (ORACLE) and for a big client we are going to set it up with XML as recommended by one comment too

For the cases (a) and (b) such a logical structure is very feasable to my experience, for case (c) I hope on the XML-solution under development ;-)

regards
Benno

RE: Database Design Using Key-Value Tables - Part 2 – Model Benefits and Drawbacks
by Benno Savioli (posted: 2/6/2006 9:21:19 AM)
just forgotten:
for the use of real datatypes we decided to set up a set of tables (one for each generic datatype and an additional one to keep keylist-items) - so at least this can be solved.
One problem with is (at least with ACCESS), that I can't make full use of system-tables to ensure relational integrity between the tables whose records I want to expand by the flexible structure as the system-tables don't allow for that 'abuse'