如何在糟糕的数据库之上构建一个好的 API？
我有一个设计糟糕的数据库。由于各种原因，它需要保持这种状态。
根据我的经验，我很喜欢 ActiveRecord 这样的 ORM，但只将它们用于没有现有数据库的新项目。
有没有一种方法可以使用 ActiveRecord 风格的 ORM 构建良好的数据模型，而无需更改设计糟糕的数据库？
I have a badly designed database. It needs to stay that way for various reasons.
In my experience I have enjoyed ORMs like ActiveRecord, but have only ever used them for new projects without an existing database.
Is there a way to make a good data model using an ActiveRecord-style ORM without changing a badly designed database?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
其他人已经触及了这方面的技术方面，所以我想添加另一种观点：
暂时停止聆听你的二元感受，并尝试专注于使用当前设计的现实世界后果。如果以此为起点，您到底会遇到哪些问题？
这样
因为归根结底，这种东西才是最重要的。如果你不能“推销”设计不好的想法，那么它确实不是，你只是在抱怨那些对除了我们这些同样生活在二进制世界中的极客同胞之外的任何人都无关紧要的东西。 >NOT（好）=坏。
当然，这个bigint列可以被替换为tinyint，并且这些列应该被移动到另一个表，并且这段重复的逻辑可能被隐藏在某些视图/函数后面，并且这个API将比必要的慢，但是这些都是一些蹩脚的细节，在非二元世界中可能重要也可能不重要。
我在工作时有一张最喜欢但又讨厌的桌子。大约 1% 的数据不一致并且完全错误。清理最后 1% 的成本将是巨大的（整合来自多个系统的数据），并且聚合时错误甚至不会显示在小数中。事实上，是我有问题。我无法向表添加特定约束。因此，我必须向使用它的 2 个程序添加一个 where 谓词。我曾多次尝试提出解决这个问题的理由，但没有人愿意投资于没有问题的东西。我同意他们的观点。
Others have touched on the technical side of this, so I want to add another view:
Stop listening to your binary feelings for a while, and try to focus on the real-world consequences of working with the current design. Exactly what are the problems you will experience if using it as a starting point?
and
Because in the end, this kind of stuff is really all that matters. If you can't "sell" the idea that the design is bad, it really isn't and you are just bitching over stuff that doesn't matter to anyone other than us fellow geeks who also live in the binary world where
NOT(Good) = Bad.Sure this bigint column could have been replaced with a tinyint, and those columns should have been moved to another table, and this piece of repeated logic could have been hidden behind some view/function, and this API will be slower than neccesary, but these are all crappy details that may or may not matter in the non-binary world.
I have a favourite table to hate at work. Approximately 1% of the data is inconsistent and just plain wrong. The cost for cleaning up this last 1% would be huge (consolidated data from multiple systems) and the errors don't even show up in the decimals when aggregated. In fact, it is me who have a problem. I can't add a particular constraint to the table. So instead I have to add a where predicate to the 2 programs using it. I've tried several times to make a case for fixing it, but nobody is willing to invest in something that isn't a problem. And I agree with them.
像这样的问题有很多好的答案。有些比其他更好。理想情况下，我可以想到两种解决方案，并且它们都源于相同的想法。装饰师。
因此，如果数据库设计很差，那么提高代码质量的最佳方法是提出正确的域模型，并在数据库之上装饰一个层，以便与底层数据模型正确配合。
第一种方法是：
1_ 大多数 ORM 允许将多个表表示为单个实体，反之亦然。但这个解决方案很复杂并且充满危险。
2_ 我的首选解决方案是使用数据库反规范化技术（例如视图、物化视图和过程）在数据模型之上创建一个新层，并在该层之上创建一个 ORM。 （最好创建一个新架构并在所有者架构上创建 view\mv。这样，任何使用旧架构的应用程序都可以继续工作，并且您可以完全控制如何设计数据访问层。）。
Questions like this have multiple good answers. Some better than other. Ideally I can think of two solution and both of them stem from the same idea. Decorator.
So if the database design is poor, then the best way to improve your code quality is to come up with the correct domain model and decorate a layer on top of your database to work correctly with your underlying data model.
The first way to do is:
1_ Most ORM allows a way to represent multiple tables in to a single entity and vice versa. But this solution is complex and fraught with danger.
2_ My preferred solution would be to use database de-normalization techniques like View, Materialized Views and Procedures to create a new layer on top of your data model and create a ORM on top of this layer. ( Preferably create a new schema and create view\mv on the owner schema. This way any application which use the older schema can continue to work and you have complete control on how you want to design your data access layer.).
我将你的问题重写为：
所以我对你的数据库的回答是：
I rewrote your question as:
So my answer for your database is:
想一想这个问题。真的。
我的房子有潮湿问题。由于各种原因，它需要保持这种状态。
根据我的经验，我喜欢抹灰和壁纸等装饰，但只在没有潮湿问题的房子里使用过它们。
有没有办法通过抹灰、壁纸等装饰，打造一个没有潮湿问题的房子，而不改变房子本身的潮湿问题？
Ponder this for a second. Really.
I have a house with moist problems. It needs to stay that way for various reasons.
In my experience I have enjoyed decoration like plastering and wallpaper, but have only ever used them in houses that didn't have moist problems.
Is there a way to create a house without moist problems using decorations like plastering and wallpaper, without changing the house-with-moist problems itself ?
这取决于该数据库中有什么可怕的事情。如果数据库有错误的表名和列名、错误的列数据类型或错误的访问层（如存储过程、视图等），您仍然可以使用您最喜欢的 ORM 并将错误的数据库映射到良好的模型。但如果数据库与某些范式不匹配，则可能会导致映射到模型时出现问题。
It depends what terrible is in that database. If database has bad table and column names, bad columns databatypes or bad access layer (like stored procedures, views etc) you still may use your favorite ORM and map bad database to nice model. But if database doesn't match some normal form, it may cause problems with mapping to model.
我唯一能想到的是，您可以根据自己的方便“重命名”数据库字段（如果不是这样的话，则可以保持一致的命名约定），因为大多数 ORM 都允许这样做。
对于其余的，在数据库模式上做你能做的：做“软改变”，比如在适当的时候添加新字段或索引，不应该破坏数据库上运行的现有代码（如果这是你不能改变模式的主要原因） 。
The only thing I can think of, is that you could "rename" the database fields as your convenience (in order to have a consistent naming convention if it wasn't the case), since most ORMs allow that.
For the rest, do what you can on the DB schema: doing "soft changes" like adding new fields or indexes when appropriate, should not break existing code running on the DB (if it's the main reason why you can't change schema).
我不相信，但这并不重要。在数据库级别管理它确实非常简单。 （但是简单并不一定意味着容易。）
可更新视图实现逻辑数据独立性。以应用程序代码无法判断它是在读取和写入视图还是基表的方式构建可更新视图。
这些可更新的视图将应用程序代码与基表的结构隔离开来。现在您可以自由地修复设计。当您更改基表时，请更改视图以保持其行为一致。
至于是否可以使用 ORM 来创建良好的数据模型而不更改设计糟糕的数据库，我会说，“不要指望它。”
I don't believe that, but it doesn't really matter. Managing this at the database level is really pretty simple. (But simple doesn't necessarily mean easy.)
Updatable views implement logical data independence. Build updatable views in such a way that application code can't tell whether it's reading and writing a view or a base table.
Those updatable views insulate application code from the structure of the base tables. Now you're free to fix the design. When you change the base tables, change the views to keep their behavior consistent.
As to whether you can use an ORM to make a good data model without changing a badly designed database, I'd say, "Don't count on it."