在数据库中存储不同的值类型 [英] Storing diverse value types in a database

问题描述

我需要将键值对存储在数据库中，其中key是例如一个字符串，值可以是多种类型之一（integer，string，float / date，GUID，BLOB）。数据库是通过OLE DB访问的，所以我需要坚持正常类型。

I need to store key-value pairs in a database, where key is either e.g. a string, and value can be one of multiple types (integer, string, float/date, GUID, BLOB). The Database is accessed through OLE DB, so I want need to stick to "normal" types.

他们的键可能涉及存在查询，不涉及值在查询中（即我不会查询所有值为17的键）。

They keys might be involved in "exists" queries, values are not involved in queries (i.e. I won't query for "all keys where value is 17"). Additional key-value-pairs will be added later.

我目前看到以下选项：

> 1。序列化BLOB

序列化键值集（此功能已可用）并将其存储为单个blob。

1. Serialized BLOB
Serializing the key-value-set (this functionality is already available) and store it as a single blob.

我唯一遇到的问题是，当共享数据库时，不能轻易更新各个值。现在是一个问题（现在的数据集只有当数据库被专门打开时才更新），但是似乎是对未来访问的限制。

The only problem I have with this is that individual values cannot be updated easily when the database is shared. It's nto a problem right now (current set of values is updated only when the DB is opened exclusively) but seems like a limitation for future access.

2。密钥BLOB

一行将由存储原始数据的 Key，Type，BLOB 组成。使一些丑陋的转换和测试，但将允许更容易地扩展var类型。我不知道存储BLOB的开销是多么糟糕，但是项目数量很少（大约十几个。

2. Key-BLOB
A Row would consist of Key, Type, BLOB storing the raw data. makes for some ugly conversion and testing, but would allow to extend the var types later easily. I have no idea how bad the overhead is for storign BLOB's, but the number of items is low (around a dozen.

3。每个值的一列键入

一行将由 Key，Type，int，double，sting，blob 组成，类型将指示使用哪一列

3. One column for each Value Type
A Row would consist of Key, Type, int, double, sting, blob, the type would indicate which column is used. Looks horrible to me, but also least "abusive".

4。每个设置一列

（使用

4. One column per setting
(using only one row). I am not really considering this.

想法？评论？其他方法？

Ideas? Comments? Other approaches?

推荐答案

另一种选择是使用每个类型的一个表，使用一个视图可以使所有的键在同一时间可见。在这里，你可以添加一个列，告诉你值的类型，所以你可以获取这个。

Another option would be to use one table per type. Use a view to make all keys visible at the same time. Here, you can add a column which tells you the type of the value so you can fetch that, too.

create view KEY_TABLES as
select key, 'INT_TABLE' from INT_TABLE
union
select key, 'STRING_TABLE' from STRING_TABLE
...

这篇关于在数据库中存储不同的值类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！