程序员应遵循的数据库设计规则
我们正在开发一个地图应用程序，它使用 Google Maps API 在地图上显示点。当前所有点均从 MySQL 数据库（保存约 5M + 记录）中获取。目前，所有实体都存储在单独的表中，其属性代表各个属性。
这会带来以下问题：
每次出现新属性时，我们都必须对数据库、应用程序代码和前端进行更改。这一切都很好，但必须为所有实体添加一些属性，因此，遍历 50 多个不同的表并添加新属性就变成了一场噩梦。
无法找到共享任何给定财产的所有实体，例如无法找到所有拥有地理系的学校/学院或大学（无需分别查询学校、大学和学院）。
删除属性同样痛苦。
没有在各个表中定义属性的标准。相同的属性可以以不同的名称或数据类型存在于另一个表中。
无法根据点的属性链接或分组点（以某种方式与点 2 相关）。
我们正在考虑重新设计整个数据库，但是如果没有DBA的帮助并且缺乏专业的数据库设计经验，我们真的很挣扎。
我们在新设计中面临的另一个问题是实体之间有很多共享的属性/属性。
例如：
名为“大学”的实体有 100 多个属性。其他实体（例如医院、银行等）与大学共享相当多的属性，例如自动取款机、停车场、自助餐厅等。
我们真的不想在单独的表中拥有属性[然后将它们链接回带有外键的实体] 因为这需要我们手动添加/删除。此外，概括属性将导致包含 50 多个属性的组。并非所有记录（即实体）都需要这些属性。
因此，牢记这一点，这就是我们对新设计的思考：
- 其中包含一些基本信息，例如 ID、名称等。
- 个表属性类型和属性来存储属性信息。
使用多对多关系将每个实体（或表，如果您愿意）链接到属性。
通过外键将地址存储在称为地址链接实体的不同表中。
我们认为这将使我们在添加、删除或查询属性时更加灵活。
然而，这种设计将导致在获取数据时增加连接数量，例如显示给定大学的所有“属性”，我们可能有一个包含 20 多个连接的查询来获取单行中的所有相关属性。
我们迫切需要了解这种设计方法中的一些意见或可能的缺陷。
感谢您抽出时间。
We are working on a mapping application that uses Google Maps API to display points on a map. All points are currently fetched from a MySQL database (holding some 5M + records). Currently all entities are stored in separate tables with attributes representing individual properties.
This presents following problems:
Every time there's a new property we have to make changes in the database, application code and the front-end. This is all fine but some properties have to be added for all entities so that's when it becomes a nightmare to go through 50+ different tables and add new properties.
There's no way to find all entities which share any given property e.g. no way to find all schools/colleges or universities that have a geography dept (without querying schools,uni's and colleges separately).
Removing a property is equally painful.
No standards for defining properties in individual tables. Same property can exist with different name or data type in another table.
No way to link or group points based on their properties (somehow related to point 2).
We are thinking to redesign the whole database but without DBA's help and lack of professional DB design experience we are really struggling.
Another problem we're facing with the new design is that there are lot of shared attributes/properties between entities.
For example:
An entity called "university" has 100+ attributes. Other entities (e.g. hospitals,banks,etc) share quite a few attributes with universities for example atm machines, parking, cafeteria etc etc.
We dont really want to have properties in separate table [and then linking them back to entities w/ foreign keys] as it will require us adding/removing manually. Also generalizing properties will results in groups containing 50+ attributes. Not all records (i.e. entities) require those properties.
So with keeping that in mind here's what we are thinking about the new design:
Have separate tables for each entity containing some basic info e.g. id,name,etc etc.
Have 2 tables attribute type and attribute to store properties information.
Link each entity (or a table if you like) to attribute using a many-to-many relation.
Store addresses in different table called addresses link entities via foreign keys.
We think this will allow us to be more flexible when adding, removing or querying on attributes.
This design, however, will result in increased number of joins when fetching data e.g.to display all "attributes" for a given university we might have a query with 20+ joins to fetch all related attributes in a single row.
We desperately need to know some opinions or possible flaws in this design approach.
Thanks for your time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在没有更具体的例子的情况下尝试概括您的问题时，很难真正批评您的方法。如果您想进行更深入的分析，请尝试绘制ER 图< /a>.
如果您的数据模型变化太大，以至于您不断添加/删除属性，并且其中许多属性重叠，那么您最好使用 EAV。
否则，如果您想要维护关系方法，但发现属性有很多重叠，您可以分析实体并查找链接到它们的抽象。
例如）我的数据库有小狗、小猫和海象，它们都具有 hasFur 和 FurColor 属性。从 3 个表中删除这些属性，并创建一个链接到这 3 个表中的每一个的 FurryAnimal 表。
当然，最简单的答案是不要触及数据模型。相反，在可用于解决问题的基础表上创建视图 (5) 、（4）和（2）
In trying to generalize your question without more specific examples, it's hard to truly critique your approach. If you'd like some more in depth analysis, try whipping up an ER diagram.
If your data model is changing so much that you're constantly adding/removing properties and many of these properties overlap, you might be better off using EAV.
Otherwise, if you want to maintain a relational approach but are finding a lot of overlap with properties, you can analyze the entities and look for abstractions that link to them.
Ex) My Db has Puppies, Kittens, and Walruses all with a hasFur and furColor attribute. Remove those attributes from the 3 tables and create a FurryAnimal table that links to each of those 3.
Of course, the simplest answer is to not touch the data model. Instead, create Views on the underlying tables that you can use to address (5), (4) and (2)
1 不可能成为问题。有一个地方可以定义您的对象。其他一切都是由此产生/派生的。只需重构您的代码，直到出现这种情况为止。
2 通过元模型来解决，您可以在元模型中描述哪些属性在哪里。这可能也是 1 所需要的。
您可能希望通过在 Seaside 上的 Smalltalk 中进行编程来完全避免该问题/glass.gemstone.com" rel="nofollow">Gemstone 面向对象的数据库。然后你可以只拥有带有集合的对象，而不需要那么多的连接。
1 cannot be an issue. There is one place where your objects are defined. Everything else is generated/derived from that. Just refactor your code until this is the case.
2 is solved by having a metamodel, where you describe which properties are where. This is probably needed for 1 too.
You might want to totally avoid the problem by programming this in Smalltalk with Seaside on a Gemstone object oriented database. Then you can just have objects with collections and don't need so many joins.