Key value pairs in relational database

Question

Does someone have experience with storing key-value pairs in a database? I've been using this type of table: CREATE TABLE key_value_pairs ( itemid varchar(32) NOT NULL, itemkey varchar(32) NOT NULL, itemvalue varchar(32) NOT NULL, CONSTRAINT ct_primarykey PRIMARY KEY(itemid,itemkey) ) Then for example the following rows could exist: itemid itemkey itemvalue ---------------- ------------- ------------ 123 Colour Red 123 Size Medium 123 Fabric Cotton The trouble with this scheme is the SQL syntax required to extract data is quite complex. Would it be better to just create a series of key/value columns? CREATE TABLE key_value_pairs ( itemid varchar(32) NOT NULL, itemkey1 varchar(32) NOT NULL, itemvalue1 varchar(32) NOT NULL, itemkey2 varchar(32) NOT NULL, itemvalue2 varchar(32) NOT NULL, . . .etc . . . ) This will be easier and faster to query but lacks the extensibility of the first approach. Any advice? JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

Before you continue on your approach, I would humbly suggest you step back and consider if you really want to store this data in a "Key-Value Pair"table. I don't know your application but my experience has shown that every time I have done what you are doing, later on I wish I had created a color table, a fabric table and a size table. Think about referential integrity constraints, if you take the key-value pair approach, the database can't tell you when you are trying to store a color id in a size field Think about the performance benefits of joining on a table with 10 values versus a generic value that may have thousands of values across multiple domains. How useful is an index on Key Value really going to be? Usually the reasoning behind doing what you are doing is because the domains need to be "user definable". If that is the case then even I am not going to push you towards creating tables on the fly (although that is a feasible approach). However, if your reasoning is because you think it will be easier to manage than multiple tables, or because you are envisioning a maintenance user interface that is generic for all domains, then stop and think really hard before you continue.