如何在插入时自动映射数据库查找表 (Oracle)
我现有的表几乎都是非规范化的。没有用于列出状态、类型、国家/地区等的事物的查找表...这个原始设计只是为了简化应用程序对数据库的访问，因此这种非规范化没有性能原因。
这导致表中有大量重复数据，我想通过引入各种状态/类型/国家/地区列的查找表来正确规范化。
是否有一些我可以在数据库（oracle）中执行此操作，并且对客户端保持透明？应用程序将继续执行插入操作，但数据库会将内容映射到幕后的正确查找表。
我一直在尝试使用视图和触发器的组合来完成映射，但感觉应该有一种更自动的方法来执行此操作。
I have existing tables that are pretty much denormalized. There are no lookup tables for things list status, type, country, etc... This original design was done just to simplify the application's access to the database, so there was no performance reason for this denormalization.
This has resulted in tables with tons of duplicate data, and I would like to normalize properly by introducing lookup tables for various status/type/country columns.
Is there some was I can do this in the database (oracle) that would remain transparent to clients? Applications would continue to do inserts but the database would map things to the proper lookup tables behind the scenes.
I've been experimenting with a combination of views and triggers that will do the mapping, but it feels like there should be a more automatic way of doing this.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在一般情况下，如果您可以创建可更新的视图，则可以使更改对用户透明。
原始的、非规范化的基表。
原始基表的权限。
任何尝试对原始基表进行 SELECT、INSERT、UPDATE 或 DELETE 操作的客户端软件都将访问可更新视图。 （这是因为表和视图共享一个命名空间，这并非偶然。）dbms 和您的支持代码将确保正确的事情发生。
根据您的平台和分解，构建可更新的视图可能很容易，也可能不可能。在 Oracle 上，我认为最糟糕的情况是您必须编写 INSTEAD OF 触发器来支持所有查询操作。那还不错。
但根据几个月来的研究，我不得不说我不是 100% 确信你真的需要这样做，或者你真的想这样做。将您的表的 DDL 和代表性示例数据发布为 SQL INSERT 语句，我们可以提供更好、更具体的建议。
In the general case, you can make your changes transparent to the users if you can create updatable views.
the original, denormalized base table.
permissions on the original base table.
Any client software that tries to SELECT, INSERT, UPDATE, or DELETE the original base table will hit the updatable view instead. (That's because tables and views share a namespace, and that's not an accident.) The dbms and your supporting code will make sure the Right Thing happens.
Depending on your platform and decomposition, building an updatable view might be easy, and it might be impossible. On Oracle, I think the worst case is that you'd have to write INSTEAD OF triggers to support all the query operations. That's not too bad.
But based on a few months knocking around on SO, I have to say I'm not 100% confident you really need to do this, or that you really want to do this. Post your tables' DDL and representative sample data as SQL INSERT statements, and we can offer better, more concrete suggestions.