OK, I don't know whether this question belong to this place, but you will suggest me if I'm wrong.
好的，我不知道这个问题是否属于这个地方，但如果我错了，你会建议我。
I have some entities which has almost same attributes, differences is in maybe 2-3 columns.
我有一些实体具有几乎相同的属性，差异可能在2-3列。
Because of those different columns, I can't create one table with columns that are union of attributes of every entity, because new entity type will require changing table design adding new columns specific to that entity type.
由于这些不同的列，我无法创建一个表，其中列是每个实体的属性联合，因为新的实体类型将需要更改表设计，添加特定于该实体类型的新列。
Instead, currently working design is that every specific entity has own table.
相反，当前工作设计是每个特定实体都有自己的表。
But, if new type of entity come on scene, I must create new table, which is totally bad idea.
但是，如果新类型的实体出现，我必须创建新表，这是完全不好的主意。
How can I create one table which consists shared attributes for each type of entity, and some additional mechanism to evidence entity-unique attributes?
如何创建一个包含每种类型实体的共享属性的表，以及一些用于证明实体唯一属性的其他机制？
So, idea is to easy add new types of objects, without changing database design, configuring only part that deal with unique columns.
因此，想法是在不更改数据库设计的情况下轻松添加新类型的对象，仅配置处理唯一列的部分。
P.S. Maybe I'm not clear, but I will add more description if is it needed.
附：也许我不清楚，但如果需要，我会添加更多描述。
3 个解决方案
#1
2
I had a design like that once. What I did was I created a table that housed all the shared properties. Then, I had separate tables for the distinct values. I used joins to match a specific entity to its shared table row. I had less than 10, so my views that used unions I just updated when I added a new entity. But, if you used a naming convention, you could write stored procs that find the table names dynamically and do the unions and joins on the fly. In my case, I used a base class and specific classes to make a custom data layer.
我曾经有过这样的设计。我做的是创建了一个包含所有共享属性的表。然后，我有不同值的单独表。我使用连接将特定实体与其共享表行匹配。我只有不到10个，所以我的观点使用了我刚刚在添加新实体时更新的工会。但是，如果您使用了命名约定，则可以编写存储过程，动态查找表名，并动态执行联合和联接。在我的例子中，我使用基类和特定的类来创建自定义数据层。
Another possibility is to have a generic table that's basically name/value pairs and a table the represents your shared properties. By joining the tables together, you could have any number of entity specific properties for your entities. It's not very efficient and the SQL would get weird, but I've seen it done.
另一种可能性是拥有一个通用表，它基本上是名称/值对，一个表代表您的共享属性。通过将表连接在一起，您可以为实体提供任意数量的实体特定属性。这不是很有效，SQL会变得奇怪，但我已经看到它完成了。
#2
2
One solution is to store the common parts in one table, and the specific parts in tables specific to that entity.
一种解决方案是将公共部分存储在一个表中，并将特定部分存储在特定于该实体的表中。
eg: To have a set of people, some of whom are managers...
例如：拥有一组人，其中一些人是经理......
Person Table
人员表
PersonID
PersonName
Manager Table
经理表
ManagerID
PersonID
DepartmentManaged
As soon as you go down the path of having one table with variable field meanings - effectively an Entity Attribute Value design - you find yourself in querying hell.
一旦你走上了一个具有可变字段含义的表的路径 - 实际上是一个实体属性值设计 - 你会发现自己在查询地狱。
#3
1
Perhaps not the best or most academic, but what about this kind of "open structure" ?
也许不是最好的或最具学术性的，但这种“开放式结构”呢？
MainTable: all common fields
MainTable：所有常见字段
SpecialProperties: extra properties, as required
- MainRecordId (P, F->MainTable)
- PropertyName (P)
- PropertyText
- PropertyValue (for numeric values)
SpecialProperties：额外属性，根据需要 - MainRecordId（P，F-> MainTable） - PropertyName（P） - PropertyText - PropertyValue（用于数值）
#1
2
I had a design like that once. What I did was I created a table that housed all the shared properties. Then, I had separate tables for the distinct values. I used joins to match a specific entity to its shared table row. I had less than 10, so my views that used unions I just updated when I added a new entity. But, if you used a naming convention, you could write stored procs that find the table names dynamically and do the unions and joins on the fly. In my case, I used a base class and specific classes to make a custom data layer.
我曾经有过这样的设计。我做的是创建了一个包含所有共享属性的表。然后，我有不同值的单独表。我使用连接将特定实体与其共享表行匹配。我只有不到10个，所以我的观点使用了我刚刚在添加新实体时更新的工会。但是，如果您使用了命名约定，则可以编写存储过程，动态查找表名，并动态执行联合和联接。在我的例子中，我使用基类和特定的类来创建自定义数据层。
Another possibility is to have a generic table that's basically name/value pairs and a table the represents your shared properties. By joining the tables together, you could have any number of entity specific properties for your entities. It's not very efficient and the SQL would get weird, but I've seen it done.
另一种可能性是拥有一个通用表，它基本上是名称/值对，一个表代表您的共享属性。通过将表连接在一起，您可以为实体提供任意数量的实体特定属性。这不是很有效，SQL会变得奇怪，但我已经看到它完成了。
#2
2
One solution is to store the common parts in one table, and the specific parts in tables specific to that entity.
一种解决方案是将公共部分存储在一个表中，并将特定部分存储在特定于该实体的表中。
eg: To have a set of people, some of whom are managers...
例如：拥有一组人，其中一些人是经理......
Person Table
人员表
PersonID
PersonName
Manager Table
经理表
ManagerID
PersonID
DepartmentManaged
As soon as you go down the path of having one table with variable field meanings - effectively an Entity Attribute Value design - you find yourself in querying hell.
一旦你走上了一个具有可变字段含义的表的路径 - 实际上是一个实体属性值设计 - 你会发现自己在查询地狱。
#3
1
Perhaps not the best or most academic, but what about this kind of "open structure" ?
也许不是最好的或最具学术性的，但这种“开放式结构”呢？
MainTable: all common fields
MainTable：所有常见字段
SpecialProperties: extra properties, as required
- MainRecordId (P, F->MainTable)
- PropertyName (P)
- PropertyText
- PropertyValue (for numeric values)
SpecialProperties：额外属性，根据需要 - MainRecordId（P，F-> MainTable） - PropertyName（P） - PropertyText - PropertyValue（用于数值）