I have a table called 'Fields' that contains application fields. In this table I store metadata for each fields (TableName, ColumnName, JoinType (inner/outer).
我有一个名为'Fields'的表,其中包含应用程序字段。在此表中,我存储每个字段的元数据(TableName,ColumnName,JoinType(内部/外部))。
I have something working but it's not as clean as I'd like.
我有一些工作,但它不像我想的那么干净。
Has anyone tackled this before?
以前有人解决过这个问题吗?
I'm looking for some ideas around better practices.
我正在寻找有关更好实践的一些想法。
2 个解决方案
#1
Dynamic data / fields are always fun.
动态数据/字段总是很有趣。
The way I've approached this before is to have a table which defines the fields I want. A very basic example is:
之前我接触过的方法是有一个表定义我想要的字段。一个非常基本的例子是:
GroupId int, <- allows me to group fields together for a common purpose
GroupId int,< - 允许我将字段组合在一起以达到共同目的
FieldId int, <- unique identifier for joins
FieldId int,< - 连接的唯一标识符
FieldName varchar(100), <- obvious
FieldName varchar(100),< - 明显
DataType int <- joins on a table which contains available types such as text, phone, email, that may have special processing characteristics.
DataType int < - 连接到包含可能具有特殊处理特征的可用类型(如文本,电话,电子邮件)的表上。
DisplayOrder int, <- what order will the fields be shown on the screen.
DisplayOrder int,< - 字段在屏幕上显示的顺序。
Then I have another table to hold the actual data: EntryId int, <- Groups the values to a unique entry point. GroupId int, FieldId int, Value varchar(max) <-use whatever number you feel will contain the data; or max if you have the right version of sql server
然后我有另一个表来保存实际数据:EntryId int,< - 将值组合到一个唯一的入口点。 GroupId int,FieldId int,Value varchar(max)< - 使用您认为包含数据的任何数字;如果你有正确版本的sql server,则为max
Finally, I dynamically generate views which turn the actual data 90 degrees for easier reporting.
最后,我动态生成视图,将实际数据转换为90度,以便于报告。
By doing it this way, you have a lot of freedom on what you're collecting without modifying the underlying code.
通过这种方式,您可以在不修改底层代码的情况下*收集内容。
#2
I wrote a query generator in the past, that could join tables automatically and put things together to generate dynamic reports.
我过去编写了一个查询生成器,它可以自动连接表并将各个部分放在一起以生成动态报告。
I think, today, I would take NHibernate. Although it is a ORM, it manages joins and creates the queries according to the mapping data that is quite similar to your meta data.
我想,今天,我会采取NHibernate。虽然它是一个ORM,但它根据与元数据非常相似的映射数据来管理连接并创建查询。
Of course you still would have to do specific things that are not covered by NHibernate. But it's an excellent start.
当然,您仍然需要做NHibernate未涵盖的具体事情。但这是一个很好的开始。
#1
Dynamic data / fields are always fun.
动态数据/字段总是很有趣。
The way I've approached this before is to have a table which defines the fields I want. A very basic example is:
之前我接触过的方法是有一个表定义我想要的字段。一个非常基本的例子是:
GroupId int, <- allows me to group fields together for a common purpose
GroupId int,< - 允许我将字段组合在一起以达到共同目的
FieldId int, <- unique identifier for joins
FieldId int,< - 连接的唯一标识符
FieldName varchar(100), <- obvious
FieldName varchar(100),< - 明显
DataType int <- joins on a table which contains available types such as text, phone, email, that may have special processing characteristics.
DataType int < - 连接到包含可能具有特殊处理特征的可用类型(如文本,电话,电子邮件)的表上。
DisplayOrder int, <- what order will the fields be shown on the screen.
DisplayOrder int,< - 字段在屏幕上显示的顺序。
Then I have another table to hold the actual data: EntryId int, <- Groups the values to a unique entry point. GroupId int, FieldId int, Value varchar(max) <-use whatever number you feel will contain the data; or max if you have the right version of sql server
然后我有另一个表来保存实际数据:EntryId int,< - 将值组合到一个唯一的入口点。 GroupId int,FieldId int,Value varchar(max)< - 使用您认为包含数据的任何数字;如果你有正确版本的sql server,则为max
Finally, I dynamically generate views which turn the actual data 90 degrees for easier reporting.
最后,我动态生成视图,将实际数据转换为90度,以便于报告。
By doing it this way, you have a lot of freedom on what you're collecting without modifying the underlying code.
通过这种方式,您可以在不修改底层代码的情况下*收集内容。
#2
I wrote a query generator in the past, that could join tables automatically and put things together to generate dynamic reports.
我过去编写了一个查询生成器,它可以自动连接表并将各个部分放在一起以生成动态报告。
I think, today, I would take NHibernate. Although it is a ORM, it manages joins and creates the queries according to the mapping data that is quite similar to your meta data.
我想,今天,我会采取NHibernate。虽然它是一个ORM,但它根据与元数据非常相似的映射数据来管理连接并创建查询。
Of course you still would have to do specific things that are not covered by NHibernate. But it's an excellent start.
当然,您仍然需要做NHibernate未涵盖的具体事情。但这是一个很好的开始。