What must i consider in database design for any new application that ought to have the ability to support the most typical relational database systems (SQL Server, MySQL, Oracle, PostgreSQL ...)?
Could it be even well worth the effort? Do you know the issues?
Rapid response is to stay with features which are standardly, or near to standardly implemented. What this signifies in greater detail is:
Avoid something that uses the database's procedural language (saved methods or triggers) as this is in which the huge variations between your systems are available in. You may want to rely on them to emulate some features, try not to rely on them to produce your personal functionality.
Separate auto-increment fields' sequences in the fields themselves. This can look a little forced for MSSQL and can implement cleanly in Oracle, DB/2 etc without requiring any emulation fixes.
Keep char and varchar fields underneath the littlest maximum size for that group of engines you are striving at.
When you are writing queries use full JOIN syntax, and bracket the JOINs to ensure that each join is from a single table and bracketed expression.
Keep date handling logic within the code, not the queries, since many of the date functions are outdoors the conventional. (For instance: if you wish to get stuff within the last two days calculate the date two days ago in code and employ that within the query.)
Beyond the effort involved should not be too intimidating, so this could be worthwhile.
I presently support Oracle, MySQL, and SQLite. And to tell the truth it's difficult. Some recommendations could be:
- avoid saved methods, but you might need these to emulate missing features on some platform (see below)
- learn to use triggers, because you will need these to emulate missing features (for instance with Oracle you do not have auto-increment, so you have to emulate it, and the ideal choice is by using triggers)
- possess a decent test atmosphere because you will need to test plenty of SQL before knowing without a doubt it's doing that which you wan on all of your platforms.
Could it be worthwhile... well is dependent. In a commercial sense it makes it worth while for enterprise level programs, however for your blog or say an internet site you may as well stick to one platform if you're able to.
Personally, I'd think carefully concerning the roi here.
It always sounds like a good idea to have the ability to hook up with any back finish in order to change back finishes if you like, but this hardly ever occur in The Real Life in my opinion.
It could come out that you might cover 95% of the potential clients by supporting just Oracle &lifier SQL Server (or MySQL &lifier SQL Server, or... etc.).
Seek information prior to going any more, and best of luck!
I am likely to plagerize johnstok's answer of just one) Avoid using saved methods and a pair of) Avoid using vendor specific SQL, and increase it.
Additionally you requested, "Could it be even well worth the effort?". I'd say... maybe; I authored a free bug tracker, BugTracker.Internet, that's according to SQL Server. You will find many developrs who simply wouldn't try it out simply because they prefer to stay with the technologies they're confident with. And, after i was thinking about beginning a hosting service, I observed that devoted Linux virtual servers tend to be less expensive than Home windows (non-virtual) services. I possibly could theoretically run the C# under mono, but my SQL is really SQL Server specific (despite the fact that I do not use saved procs) it might be an enormous effort to port.
If you're focusing on a companyOrcompany market, you will find that some shops are strickly Oracle, or strictly SQL Server, which your application may be eliminated in early models from the competition in line with the technology it uses.
So, maybe being open is important, for you. What type of application could it be? Who'll utilize it?
Additionally you requested, "Do you know the ptifalls". Not testing in the process. If you are planning to aid some dbs you listed, then you ought to be testing together early and frequently as opposed to just target one while thinking it will likely be simple to convert to others. With that time you will probably find yourself within an architectural cul-p-sac.