[noob warning!] I need to store some data in some tables where it is like the equivalent of an array of pointers to polymorphic objects. E.g. (pseudo C++)
[noob警告!]我需要在一些表中存储一些数据,它类似于指向多态对象的指针数组。例如。 (伪C ++)
struct MyData { string name; }
struct MyDataA : MyData { int a,b,c; }
struct MyDataB : MyData { string s; }
MyData * data[100];
I don't really know what google search to enter! How would you store info like this in an SQL database?
我真的不知道google搜索输入了什么!你如何在SQL数据库中存储这样的信息?
My random thoughts:
我随意的想法:
- I could have one table with a column that is the struct identifier and then have redundant columns, but this seems wasteful.
- I can have one table for each struct type. These would have a foreign key back to the master array table. But, how do I point to the struct tables?
我可以有一个表,其中一列是结构标识符,然后是冗余列,但这似乎很浪费。
我可以为每个结构类型都有一个表。这些将有一个外键返回主数组表。但是,我如何指向结构表?
4 个解决方案
#1
This is a complex topic. To get an idea of the strategies involved, I suggest reading the Hibernate docs on this topic:
这是一个复杂的话题。为了了解所涉及的策略,我建议您阅读有关此主题的Hibernate文档:
http://docs.jboss.org/hibernate/stable/core/reference/en/html/inheritance.html
Even if you're not using Hibernate, the relational mapping concepts are still relevant. Note that Hibernate does not dictate any one strategy, because no one approach is better than the others.
即使你没有使用Hibernate,关系映射概念仍然是相关的。请注意,Hibernate并没有规定任何一种策略,因为没有一种方法比其他方法更好。
#2
I do the "table-per-sublcass" style from the Hibernate docs.
我从Hibernate文档中做了“table-per-sublcass”风格。
You make a Person table with all the things you know about a person, plus the PersonID. Then you make a Customer table, with only the data that's unique to a Customer (account balance, etc). Put the PersonID in the Customer table. A WebsiteUser might have a CustomerID in it, and so on down the chain.
您创建一个Person表,其中包含您对一个人的所有知识,以及PersonID。然后创建一个Customer表,其中只包含客户独有的数据(帐户余额等)。将PersonID放在Customer表中。一个WebsiteUser可能有一个CustomerID,依此类推。
One-to-one relationships mapping the IS-A inheritance relationships.
映射IS-A继承关系的一对一关系。
#3
One possibility is an XML field to store the data, this allows searching and retrieving whilst also being relatively easy to serialise. (the question says SQL, but doesn't specify a specfic vendor database, so XML may not work for every DB solution.)
一种可能性是用于存储数据的XML字段,这允许搜索和检索,同时也相对容易地序列化。 (问题是SQL,但没有指定特定的供应商数据库,因此XML可能不适用于每个数据库解决方案。)
Edit : I'm going to caveat this because it's not entirely clear what needs to be stored / retrieved / purpose etc, so XML may be entirely inappropriate - I'm throwing it out there as a thought provoker instead.
编辑:我要告诫这一点,因为它并不完全清楚需要存储/检索/目的等等,因此XML可能完全不合适 - 我把它作为一个思想挑战者扔掉了。
#4
There's really two major ways to solve this:
有两种主要方法可以解决这个问题:
- table-per-type
- table-per-hierarchy
Either of them has its pros and cons.
它们中的任何一个都有其优点和缺点。
Table-per-type gives you more tables (one per type), which only store the "delta" from the immediate super class. Worst case, you need to join together a number of tables to finally get all the data together for a single instance of a type. Pros: since you only store what's really relevant for that type into a separate table, you can do this like set NOT NULL restrictions etc. on the database table.
Table-per-type为您提供了更多的表(每种类型一个),它只存储来自直接超类的“delta”。最糟糕的情况是,您需要将多个表连接在一起,以便最终将所有数据合并为一个类型的单个实例。优点:由于您只将与该类型真正相关的内容存储到一个单独的表中,您可以像在数据库表上设置NOT NULL限制等那样执行此操作。
Table-per-hierarchy gives you less tables, but each table represents an entire hierarchy, so it will contains potentially lots of columns which aren't filled (in the rows representating base class types). Also, on the extra columns that make up the derived classes, you cannot set things like NOT NULL restrictions - all those extra columns must be nullable, since they really don't exist in the base classes, so you loose some degree of safety here.
每个层次表可以为您提供更少的表,但每个表代表一个完整的层次结构,因此它将包含许多未填充的列(在表示基类类型的行中)。此外,在构成派生类的额外列上,您不能设置NOT NULL限制之类的东西 - 所有这些额外的列必须是可空的,因为它们实际上不存在于基类中,因此您在这里放松了一定程度的安全性。
See for yourself - there are two really good articles on how to do this (in Entity Framework, but the principles apply to any database and any data mapping technology):
亲自看看 - 关于如何做到这一点有两篇非常好的文章(在实体框架中,但这些原则适用于任何数据库和任何数据映射技术):
- Demystifying The Code: Table Per Type
- Demystifying The Code: Table Per Hierarchy
揭开代码的神秘面纱:每种类型的表格
揭开代码的神秘面纱:每个层次结构的表
Hope this helps and gives you some inputs!
希望这有所帮助,并为您提供一些输入!
Marc
#1
This is a complex topic. To get an idea of the strategies involved, I suggest reading the Hibernate docs on this topic:
这是一个复杂的话题。为了了解所涉及的策略,我建议您阅读有关此主题的Hibernate文档:
http://docs.jboss.org/hibernate/stable/core/reference/en/html/inheritance.html
Even if you're not using Hibernate, the relational mapping concepts are still relevant. Note that Hibernate does not dictate any one strategy, because no one approach is better than the others.
即使你没有使用Hibernate,关系映射概念仍然是相关的。请注意,Hibernate并没有规定任何一种策略,因为没有一种方法比其他方法更好。
#2
I do the "table-per-sublcass" style from the Hibernate docs.
我从Hibernate文档中做了“table-per-sublcass”风格。
You make a Person table with all the things you know about a person, plus the PersonID. Then you make a Customer table, with only the data that's unique to a Customer (account balance, etc). Put the PersonID in the Customer table. A WebsiteUser might have a CustomerID in it, and so on down the chain.
您创建一个Person表,其中包含您对一个人的所有知识,以及PersonID。然后创建一个Customer表,其中只包含客户独有的数据(帐户余额等)。将PersonID放在Customer表中。一个WebsiteUser可能有一个CustomerID,依此类推。
One-to-one relationships mapping the IS-A inheritance relationships.
映射IS-A继承关系的一对一关系。
#3
One possibility is an XML field to store the data, this allows searching and retrieving whilst also being relatively easy to serialise. (the question says SQL, but doesn't specify a specfic vendor database, so XML may not work for every DB solution.)
一种可能性是用于存储数据的XML字段,这允许搜索和检索,同时也相对容易地序列化。 (问题是SQL,但没有指定特定的供应商数据库,因此XML可能不适用于每个数据库解决方案。)
Edit : I'm going to caveat this because it's not entirely clear what needs to be stored / retrieved / purpose etc, so XML may be entirely inappropriate - I'm throwing it out there as a thought provoker instead.
编辑:我要告诫这一点,因为它并不完全清楚需要存储/检索/目的等等,因此XML可能完全不合适 - 我把它作为一个思想挑战者扔掉了。
#4
There's really two major ways to solve this:
有两种主要方法可以解决这个问题:
- table-per-type
- table-per-hierarchy
Either of them has its pros and cons.
它们中的任何一个都有其优点和缺点。
Table-per-type gives you more tables (one per type), which only store the "delta" from the immediate super class. Worst case, you need to join together a number of tables to finally get all the data together for a single instance of a type. Pros: since you only store what's really relevant for that type into a separate table, you can do this like set NOT NULL restrictions etc. on the database table.
Table-per-type为您提供了更多的表(每种类型一个),它只存储来自直接超类的“delta”。最糟糕的情况是,您需要将多个表连接在一起,以便最终将所有数据合并为一个类型的单个实例。优点:由于您只将与该类型真正相关的内容存储到一个单独的表中,您可以像在数据库表上设置NOT NULL限制等那样执行此操作。
Table-per-hierarchy gives you less tables, but each table represents an entire hierarchy, so it will contains potentially lots of columns which aren't filled (in the rows representating base class types). Also, on the extra columns that make up the derived classes, you cannot set things like NOT NULL restrictions - all those extra columns must be nullable, since they really don't exist in the base classes, so you loose some degree of safety here.
每个层次表可以为您提供更少的表,但每个表代表一个完整的层次结构,因此它将包含许多未填充的列(在表示基类类型的行中)。此外,在构成派生类的额外列上,您不能设置NOT NULL限制之类的东西 - 所有这些额外的列必须是可空的,因为它们实际上不存在于基类中,因此您在这里放松了一定程度的安全性。
See for yourself - there are two really good articles on how to do this (in Entity Framework, but the principles apply to any database and any data mapping technology):
亲自看看 - 关于如何做到这一点有两篇非常好的文章(在实体框架中,但这些原则适用于任何数据库和任何数据映射技术):
- Demystifying The Code: Table Per Type
- Demystifying The Code: Table Per Hierarchy
揭开代码的神秘面纱:每种类型的表格
揭开代码的神秘面纱:每个层次结构的表
Hope this helps and gives you some inputs!
希望这有所帮助,并为您提供一些输入!
Marc