每个相似的对象一张表？
我为绘图店编写了一个应用程序。 我的系统中有这些类：商店、购物车位置、货架和面包店。
它们具有以下属性：
商店：X，Y，名称，宽度，高度，类型，地址
购物车位置：X，Y，名称，宽度，长度，类型，容量
货架：X，Y，名称，宽度，长度，类型，高度，balance_limit
面包店：X，Y，名称，宽度，长度，类型，open_hours
现在我想在我的数据库中表示这些类。 但正如您所看到的，上面的所有类都有相同的内容，例如：
X、Y、宽度、高度、名称和类型。 它们的不同之处在于：
商店：地址
购物车 地点：容量
机架：balance_limit
面包店：open_hours
我知道将来所有这些类型的对象都将拥有自己的新属性，并且它们将获得所有这些对象在一次。
我知道将会出现上面没有提到的新型物体，它们将具有与商店、购物车场所、货架和面包店相同的属性。
我想创建允许我添加新属性和新对象的数据库结构。 并添加将同时添加到每个类的新属性。 此外，我希望系统设计清晰，并允许我对其进行简单的数据库查询。
所以我的问题是：
我应该为每种类型的对象（商店、购物车、货架、面包店）制作一个数据库表，因为它会更清晰，还是应该将它们组合到一个表中，因为它们具有相似的属性列表？
我希望您能告诉我为什么一种解决方案比另一种更好。 我希望在这里得到一些实用的建议，而不仅仅是“你应该这样做，因为这是唯一正确的方法，公理”。
I write an application for drawing shops. I have these classes in my system: shop, cart place, rack and bakery.
They have this properties:
shop: X, Y, name, width, height, type, address
cart place: X, Y, name, width, length, type, capacity
rack: X, Y, name, width, length, type, height, balance_limit
bakery: X, Y, name, width, length, type, open_hours
Now I would like to represent these classes in my database. But as you can see all classes from above have same things like:
X, Y, width, height, name and type. And what make them different is:
shop: address
cart place: capacity
rack: balance_limit
bakery: open_hours
I know that in future all these types of object will have their own new properties and that they will gain new properties that all of them will have at once.
And I know that there will be new types of objects not mentioned above that will have some properties same as shops, cart places , racks and bakeries.
I would like to create database structure that will allow me to add new properties and new objects. And to add new properties that will be added to every class in the same time. And moreover I would like the system to be clearly designed and allow me to do easy database queries to it.
So my question is:
Should I make a database table for every type of object (shop, cart place, rack, bakery) because it will be more clear or should I combine it all together into one table because they have similar list of properties?
I would like you to give me some idea why one solution will be better than another. I hope to get some practical advice here not only "you should do it, because it's only right way, axiom".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这不是一个简单的问题... SQL 数据库不擅长对类层次结构进行建模。
您将需要一个好的 ORM。
我将类层次结构放入表中的做法是：
首先，我确保它是相关的：例如，将 Web CMS 的节点、文章等放入同一个表中是有意义的，因为这些都是同一事物的变体。
这个想法是，您需要创建数据库列来进行搜索、索引和进行 SQL 查询，但不需要将所有信息存储在数据库列中。 您可以将其余部分存储在 BLOB 列中的序列化对象中。
表中，有
- 当然有一列指示该行是哪个类的实例
- 所有类都共有的一些“核心”列，基本上是基类字段。
- 仅由某些子类使用的其他列，但我需要搜索，因此需要对它们建立索引
- 包含对象中所有其他数据的 BLOB。
基本上，当我在数据库中存储对象时，会根据其类填充相关列，并将其余数据（甚至整个对象）放入 BLOB 中。
这样做的好处是，如果您添加不需要搜索或索引、只需存储的成员值，则无需将其放入数据库列中，因此根本不需要对数据库进行任何更改：它将存储在序列化的 BLOB 中。 唯一要做的就是在反序列化代码中为此成员添加一个默认值，因此该类中已经存在于数据库中并且没有该成员的对象将具有一个不错的默认值。
如果您愿意，您还可以对对象格式进行版本控制，这会变得更加复杂。
然而这个方案有一些缺点：
约束很难应用：
- 您只能将约束应用于具有列的字段。
- 由于某些列仅出现在某些类中，因此数据库需要了解一些有关类层次结构的信息。
例如，您可能希望将地址放入单独的表中，并添加相关字段（邮政编码、国家/地区、街道、号码等）：将所有这些放入主表中会添加太多列。 此外，在某些时候，您会想要添加一些客户或其他位于不同表中并且也有地址的内容，因此最好将地址放在单独的表中并引用它们。
对于个人或公司等来说也是如此。
现在，我认为商店有地址，但购物车没有，因此您必须在数据库 DDL 中表示，如果表中的行是“商店”类型，则必须引用地址”，但不是“购物车”类型。
它可能会变得有点毛茸茸的。
另外，例如，如果您有 10 个商店和 100.000 个购物车，为了性能考虑，拆分桌子可能会很有趣，这样您就可以获得一张漂亮的小型快速桌子和一张大桌子。
现在还有其他解决方案：
例如，您可以将所有代码和基成员放在基类中，但将 tableName 设为在派生类中更改的类属性。 这样，只需更改表名，所有代码都适用于不同的表，但您不必重写任何内容。
然后每个班级有一张桌子。
如果您的类层次结构变得更加复杂，您当然可以在每个表上应用上面的方法。
两者如何选择？
基本上，如果您制作一个 Web CMS 并将从 Node 派生的类的对象存储在表中，例如：
- 文章
- 带有图例的图像
- 画廊
- 等等
所有这些对象基本上都是相同的东西。
它们都有一个标题、一个 TextContent 字段、属于一个 ParentNode 等。
如果您在 TextContent 中使用关键字搜索“foo”，那么如果所有对象都在同一个表中，就会容易得多。
如果您想列出 ParentNode 的所有子节点以将它们显示在网页上，那么如果所有内容都在 1 个表中，也会更容易。
所以在这种情况下第一种方法确实是有好处的。
现在就你的情况而言，这些对象并不那么相似。
就我个人而言，我什至不会给他们相同的基类。
我将创建一个 Mixin 名称为“ThingWithCooperatives”（可能更短）并将其添加到类中。
现在，也许面包店距离商店足够近，可以从商店继承，但手推车和货架可能不行。
在你的情况下，我肯定会使用几个表。 在每个表中，如果需要存储多个类，我会使用第一种方法。
最重要的是，您的类层次结构（以及表格）必须基于相关的内容（汽车经销商和面包店是商店），而不是实际上没有其他共同点的对象之间碰巧存在的共同特征（例如购物车和商店）。 为此，有 mixins 来共享公共代码，但不共享基类。
This is not an easy question... SQL databases are not good at modelling class hierarchies.
You will need a good ORM.
What I put a class hierarchy in a table, do is this :
First I make sure it is pertinent : for instance, putting Nodes, Articles, etc for a web CMS in the same table makes sense because these are all variants of the same thing.
The idea is that you need to create database columns for searching, indexing, and making SQL queries, but you don't need to store all your information in database column. You can store the rest in a serialized object in a BLOB column.
In the table, there are
- of course a column that indicates which class this row is an instance of
- a few "core" column that are common to all classes, basically the base class fields.
- other columns that are used only by some subclasses, but that I need for searching, so they need to be indexed
- a BLOB that contains all other data from the object.
Basically when I store an object in the database, the relevant columns according to its class are filled, and the rest of the data (or even the entire object) is shoved in the BLOB.
The great thing about this is, if you add a member value that doesn't need to be searched or indexed, only stored, you don't need to put it in a database column, so you make no changes to the database at all : it will be stored in the serialized BLOB. The only thing to do is add a default value for this member in your deserialization code, so the objects from this class which are already in the database and do not have this member will have a decent default value for it.
You can also version your object formats if you like, it gets more complex.
However this scheme has some drawbacks :
Constraints are difficult to apply :
- you can only apply constraints to fields that have a column.
- since some columns only occur with some classes, the database needs to know a bit about your class hierarchy.
For instance you will probably want to put the addresses in a separate table, and add the relevant fields (zipcode, country, street, number, etc) : putting all this in your main table would add too many columns. Also you will, at some point, want to add some Customers or other things that are in a different table and also have an address, so it is better to put address in a separate table and REFERENCE them.
Same thing for people or companies, etc.
Now a shop has an address but a cart does not, I presume, so you will have to express in database DDL that a row from your table must REFERENCE an address if it is of type "shop" but not of type "cart".
It can get a bit hairy.
Also if you have 10 shops and 100.000 carts, for example, it may be interesting, for performance, to split the tables, so you get a nice small fast table and one big table.
Now there are other solutions :
For instance, you could put all the code and base members in the base class, but make the tableName a class attribute that is changed in the derived class. In this way, just changing the table name, all code applies to a different table, but you do not have to rewrite any of it.
Then you get 1 table per class.
You can of course apply the method above on each table if your class hierarchy gets more complex.
How to choose between the two ?
Basically if you make a web CMS and you store in a table, objects of classes derived from Node like :
- Article
- Image with a legend
- Gallery
- etc
All these objects are basically the same stuff.
They will all have a Title, a TextContent field, belong to a ParentNode, etc
If you make a keyword search for "foo" in the TextContent, it is much easier if all objects are in the same table.
If you want to list all children of a ParentNode to display them on a web page, it is also much easier if everything is in 1 table.
So in this case the first method is really a benefit.
Now in your case, the objects are not that similar.
Personnally I would not even give them the same Base Class.
I would create a Mixin names "ThingWithCoordinates" (maybe something shorter) and add this to the classes.
Now, maybe a Bakery is close enough to a Shop that it can inherit from it, but carts and racks, probably not.
In your case I would definitely use several tables. And in each table, if you need to store several classes, I would use the first method.
Most important is that your class hierarchy (and thus tables) must be based on something RELEVANT (car dealers and bakeries are shops) and not common features that happen to exist between objects that actually have nothing else in common (like cart and shop). For this, there are mixins to share common code, but not base classes.
我的建议是：
What I suggest :
是的，您应该为每个对象使用一个表，因为它们是它们自己的实体。
如果将这些表映射到对象，您将无需连接多个表，从而提高效率。
此外，每个对象在开发和复杂性方面都是隔离的。
Yes, you should use a single table for each object as they are their own entities.
If you map these tables to objects you will eliminate the need to join multiple tables making things more efficient.
Also each Object is then isolated in terms of development and complexity.
通过“共享”通用项目的表格，您可以获得什么好处？
如果没有，那就不要这样做——只需将它们放在不同的表中（特别是如果它们将来会进一步分歧的话）。
我猜你没有使用ORM？
What benefit do you get from 'sharing' a table for the common items?
If none, then don't do it - just stick them in different tables (especially if they will diverge further in the future).
I guess you are not using an ORM?
在网上搜索“泛化专业化关系建模”。
当这种模式出现时，您会发现一些关于如何设计 SQL 数据库的好文章。 最好的文章遵循提供指导而不是制定规范规则的标准。
Search the web on "generalization specialization relational modeling".
You will find several good articles on how to design your SQL database when this pattern arises. The best of the articles follow your criterion of providing guidance rather than laying down canonical rules.
如果共性与 shop_size 类似，那么我建议为此创建一个单独的表。
原因是，通过标准化，您可以获得其他信息，因此，例如，可以轻松显示宽度和长度的下拉菜单，因为可能有许多商店具有相同的尺寸。
您还可以稍后查看此表中的数据以获取其他信息。
IMO，主要是你获得了灵活性。
If the commonality is something similar to shop_size then I would suggest creating a separate table for that.
The reason is that by normalizing you can get other information, so, for example, it makes it easy to have a drop down showing width and length as there may be many shops with the same measurements.
You can also look at the data that is in this table to get other information, later.
Mainly, you get flexibility, IMO.