创建非NoSQL关系型多模式数据库的好方法是什么? [英] What is a good approach in creating a non-NoSQL, relational multi-schema database?
问题描述
考虑一种情况,其中数据库表的架构可能会发生变化,也就是说,字段,字段的数量以及这些字段的类型可能会根据客户端ID的不同而有所不同。
以一个 Users
表为例。通常,我们将在具有以下字段的水平表中表示这一点:
- 名字
- 姓氏
- 年龄
但是,正如我提到的,每个客户可能有不同的要求。
我当时想在像SQL Server这样的关系数据库中为 Users
的用户提供一种多方案的方法。有两个表:
- UsersFieldNames-{FieldNameId,ClientId,FieldName,FieldType}
- UsersValues- {UserValueId,FieldNameId,FieldValue}
要检索数据(首先使用EntityFramework DB),我正在考虑数据透视表,使用 LINQ扩展名-数据透视扩展之类的方法可能有用。 / p>
我想知道其他满足此要求的方法
我是出于自己的好奇心而问这个问题,因为我回想起过去发生的类似对话,并且与提出的问题。
谢谢。
虽然我认为NoSQL数据库对此最有效,但我
有一个名为METATABLES的表,例如
METATABLE = {table_name ,字段名称}
和另一个
ACTUAL_DATA = {表名,字段名,actual_data_id,float_value,string_value, double_value,varchar_value}
在actual_data中,字段table_name和field_name将是外键,指向METATABLES。在METATABLES中,您定义每个客户端所需的特定字段。 ACTUAL_DATA表根据数据类型保存存储在适当值字段中的那些字段的实际值(如果该字段值为字符串,则将其存储在string_Value字段中。)
这种方法可能不是最有效的。希望对您有所帮助。
Consider a situation where the schema of a database table may change, that is, the fields, number of fields, and types of those fields may vary based on, say a client ID.
Take, for example a Users
table. Typically we would represent this in a horizontal table with the following fields:
- FirstName
- LastName
- Age
However, as I mentioned, each client may have different requirements.
I was thinking that to represent a multi-schema approach to Users
in a relational database like SQL Server, this would be done with two tables:
- UsersFieldNames - {FieldNameId, ClientId, FieldName, FieldType}
- UsersValues - {UserValueId, FieldNameId, FieldValue}
To retrieve the data (using EntityFramework DB First), I'm thinking pivot table, and the use of something like LINQ Extentions - Pivot Extensions may be useful.
I would like to know of any other approaches that would satisfy this requirement.
I'm asking this question for my own curiosity, as I recall similar conversations coming up in the past, and in relation to this question posed.
Thanks.
While I think a NoSQL data base would work best for this, I once tried something like this.
Have a table named something like METATABLES, like this
METATABLE = {table_name, field name}
and another,
ACTUAL_DATA ={table_name, field_name, actual_data_id, float_value, string_value, double_value, varchar_value}
in actual_data, the fields table_name and field_name would be foreign keys, pointing to METATABLES. In METATABLES you define the specific fields each client requires. the ACTUAL_DATA table holds the actual values of those fields, stored in the appropiate value field, depending on the data type (if the field value is a string, it would be stored in the string_Value field).
This approach is probably not the most efficient, though. Hope it helps.
这篇关于创建非NoSQL关系型多模式数据库的好方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!