Does someone have knowledge about storing key-value pairs inside a database?
I have been using this 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 instance the next rows could exist:
itemid itemkey itemvalue ---------------- ------------- ------------ 123 Colour Red 123 Size Medium 123 Fabric Cotton
The problem with this particular plan may be the SQL syntax needed to extract information is quite complex. Will it be easier to just create a number of key/value posts?
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 is simpler and faster to question but lacks the extensibility from the first approach. Any advice?
Before you decide to carry on your approach, I'd humbly suggest you take a step back and determine that you want to keep this data inside a "Key-Value Pair"table. I'm not sure the application but my experience has proven that each time I've done your work, afterwards If only I'd produced one table, a cloth table along with a size table.
Consider referential integrity constraints, for the important thing-value pair approach, the database can't let you know when you're attempting to store one id inside a size area
Consider the performance advantages of joining on the table with 10 values versus a normal value that could have 1000's of values across multiple domain names. How helpful is definitely an index on Key Value really likely to be?
Usually reasoning behind doing your work happens because the domain names have to be "user definable". If that's the situation then even I'm not likely to push you towards creating tables quickly (although that's a achievable approach).
However, in case your reasoning happens because you believe it will likely be simpler to handle than multiple tables, or since you are picturing a maintenance interface that's generic for those domain names, then stop and think very hard before you decide to continue.
From experience, i've discovered that particular secrets could be more broadly used or queried more frequently. We now have usually then slightly p-stabilized the look to incorporate a particular area in the primary "item" table.
eg. if every Item includes a Colour, you may add the color column for your item table. Fabric and Size might be used less frequently and may be stored separate within the key-value pair table. You may also keep your colour within the key-value pair table, but duplicate the information within the item table to find the performance benefits.
Clearly this varies with respect to the data and just how flexible you'll need the important thing-value pairs to become. It may also result in your attribute data not situated consistantly. However, p-normalizing does greatly simplify the queries and enhances their performance too.
I'd usually only consider p-normalizing when performance becomes and problem, not just in simplify a question.
There's another solution that falls approximately the 2. You should use an xml type column for that secrets and values. Which means you keep your itemid area, then come with an xml area that consists of the xml defined for many key value pairs like
<items> <item key="colour" value="red"/><item key="xxx" value="blah"/></items>
When you extract your computer data fro the database you are able to process the xml in many various ways. Based on your usage. It is really an extend able solution.