リレーショナルデータベースのキーバリューペア

質問

データベースにkey-valueペアを保存した経験のある方はいらっしゃいますか？

私はこのようなテーブルを使っています。

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)
)

そうすると、例えば以下のような行が存在することになります。

 itemid            itemkey        itemvalue    
 ----------------  -------------  ------------ 
 123               Colour         Red            
 123               Size           Medium             
 123               Fabric         Cotton

この方式の問題点は、データを抽出するために必要なSQL構文が非常に複雑であることです。 単に一連のキー/値列を作成する方が良いのでしょうか？

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 . . .
)

これはクエリがより簡単で速くなりますが、最初のアプローチの拡張性に欠けています。 何かアドバイスはありますか？

どのように解決するのですか？

アプローチを続ける前に、一歩下がって、このデータを本当にキーと値のペアのテーブルに格納したいのかどうか、謙虚に検討することをお勧めします。 あなたのアプリケーションを知らないのですが、私の経験では、あなたが行っていることを行うたびに、後になってから、色テーブル、生地テーブル、およびサイズ テーブルを作成しておけばよかったと思うことがあります。

参照整合性制約について考えてみましょう。キーと値のペアのアプローチを取る場合、データベースは、サイズ フィールドに色 ID を格納しようとしているときに、それを知ることができません。

複数のドメインにわたって何千もの値を持つ可能性のある一般的な値に対して、10個の値を持つテーブルで結合することのパフォーマンス上の利点について考えてみてください。 キーバリューに対するインデックスが本当に役に立つのでしょうか？

通常、あなたが行っていることを行う理由は、ドメインが "ユーザー定義可能である必要があるからです。 もしそうであれば、私でさえも、その場でテーブルを作成することを推し進めるつもりはありません (それは実現可能なアプローチですが)。

しかし、あなたの理由が、複数のテーブルよりも管理が簡単だと思うから、または、すべてのドメインに対して汎用的なメンテナンス ユーザー インターフェイスを想定しているからであるならば、続ける前に立ち止まってよく考えてみてください。