为数据库无关的应用程序设计数据库[英] Database design for database-agnostic applications

本文是小编为大家收集整理的关于为数据库无关的应用程序设计数据库的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

数据库设计中,我必须考虑一个新应用程序,该应用程序应该能够支持最常见的关系数据库系统(SQL Server,MySQL,Oracle,PostgreSQL ...)?/p>

甚至值得付出努力吗?什么是陷阱?

推荐答案

简短的答案是坚持标准或接近标准实现的功能.这更详细的是:

  • 避免使用数据库的过程语言(存储过程或触发器),因为这是系统之间的巨大差异.您可能需要使用它们来模仿某些功能,但不要使用它们创建自己的功能.

  • 将自动插入字段的序列与字段本身分开.对于MSSQL来说,这看起来会有些强制,但将在Oracle,DB/2等中干净地实现,而无需任何仿真修复程序.

  • 将Char和Varchar字段保持在您所瞄准的一组发动机的最大最大尺寸以下.

  • 您编写查询时,请使用完整的JOIN语法,然后将连接括起来,使每个联接都在单个表和包围表达式之间.

  • 日期处理逻辑保留在代码中,而不是查询中,因为许多日期函数都超出了标准. (例如:如果您想在过去的两个星期中获取两周前的日期,并在查询中使用该日期.)

除此之外

其他推荐答案

如果我是你,我会对您的投资回报率.

它总是听起来,就像一个好主意一样,能够在您喜欢的时候挂上任何后端或更改后端,但这很少在我的经验中发生在现实世界中.<<<<<<<<<<<<

可能会通过支持Oracle&SQL Server(或MySQL&SQL Server,或...等)来覆盖95%的潜在客户.

在进行进一步的过程之前,请进行研究,祝您好运!

其他推荐答案

我当前支持Oracle,MySQL和SQLite.老实说,这很艰难. 一些建议是:

  • 避免存储过程,但是您可能需要它们在某些平台上模拟缺少的功能(请参见下文)
  • 学习如何使用触发器,因为您需要它们模仿缺失的功能(例如,使用Oracle您没有自动报道,因此您需要模拟它,一个很好的选择是使用触发器)
  • 有一个不错的测试环境,因为您需要测试很多SQL,然后才能确保它在所有平台上都在做您的工作.

是值得的...良好取决于.从商业上来说,对于企业级应用程序来说是值得的,但是对于博客或说明网站,如果可以的话,您还可以坚持使用一个平台.

本文地址:https://www.itbaoku.cn/post/597601.html

问题描述

What do I have to consider in database design for a new application which should be able to support the most common relational database systems (SQL Server, MySQL, Oracle, PostgreSQL ...)?

Is it even worth the effort? What are the pitfalls?

推荐答案

The short answer is to stick to features that are standardly, or close to standardly implemented. What this means in more detail is:

  • Avoid anything that uses the database's procedural language (stored procedures or triggers) since this is where the huge differences between the systems come in. You may need to use them to emulate some features, but don't use them to create your own functionality.

  • Separate auto-increment fields' sequences from the fields themselves. This will look a bit forced for MSSQL but will implement cleanly in Oracle, DB/2 etc without needing any emulation fixes.

  • Keep char and varchar fields below the smallest maximum size for the set of engines you're aiming at.

  • When you're writing queries use full JOIN syntax, and bracket the JOINs so that each join is between a single table and bracketed expression.

  • Keep date handling logic in the code, not the queries, since a lot of the date functions are outside the standard. (For example: if you want to get stuff for the past two weeks calculate the date two weeks ago in code and use that in the query.)

Beyond that the effort involved shouldn't be too intimidating, so it may well be worth it.

其他推荐答案

If I were you, I'd think hard about the return on your investment here.

It always sounds like a great idea to be able to hook up to any back end or to change back ends whenever you like, but this very rarely happens in The Real World in my experience.

It might turn out that you may cover 95% of your potential customers by supporting just Oracle & SQL Server (or MySQL & SQL Server, or... etc.).

Do your research before going any further, and good luck!

其他推荐答案

I currently support Oracle, MySQL, and SQLite. And to be honest it's tough. Some recommendations would be:

  • avoid stored procedures, but you may need them to emulate missing features on some platform (see below)
  • learn how to use triggers, because you'll need them to emulate missing features (for example with Oracle you don't have auto-increment, so you need to emulate it, and a good choice is with triggers)
  • have a decent test environment because you'll need to test lots of SQL before knowing for sure that it's doing what you wan on all your platforms.

Is it worth it... well depends. Commercially it is worth it for enterprise level applications, but for a blog or say a website you might as well stick with one platform if you can.