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?
Advertisement
Answer
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.