How to design a database schema that can be used with both MySQL and SQL Server?

Question

I'd like to give customers a choice of the database engine, but also want to minimize my troubles of such a decision.
The engines in question are MySQL (5 or later) and SQL Server (2005 or later).

Answer 1

Google for the differences in datatypes.

But the schema is just a part of the picture.

The SQL dialects are different too. Google for those differences. Then, either stick to a subset of SQL that's common to both, or build in a scheme to use somewhat different SQL in each.

Don't wait until the end to test on the "other" db. Test on both from the start so that you don't invest too much in a dead end design direction.

Here's a starting place:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#EZD
and:
http://troels.arvin.dk/db/rdbms/#insert

Answer 2

This article describes some of the main differences.

Differences Between MSSQL and mySQL

You probably would want to handle the differences in a Data Access Layer.

Answer 3

My four main guidelines for cross-database development like this are:

1. Don't use spaces in names for anything
2. Don't use keywords from either db as column names (ORDER, DATE etc.)
3. Use the simplest column types possible (CHAR, INT). Date- and Timestamp-type column differences will probably trip you up at some point, so avoid them if possible (that's not usually realistic, I know).
4. De-normalize more than you might think is appropriate. The more complicated and JOIN-y your query is, the more likely you will end up having to maintain db-specific paired versions of your queries to get everything to work acceptably on both databases. Once you're there, you're doomed.

1 and 2 are really the same problem - all databases allow escaping object names (so you can have names with spaces and names like ORDER and DATE), but they usually do it differently, so this query for SQL Server:

SELECT [ORDER], [Why This Name] FROM [Table From Hell]

has to be

SELECT "ORDER", "Why This Name" FROM "Table From Hell"

in Oracle, and then you have two versions of each query or some uber-janky delimiter-replacement code. I usually go with the simpler approach of just not using keywords or spaces.

Answer 4

One "fun" option is to not write SQL at all. Build a system that you can abstract the spec of what you want to do and then generate the schema/queries etc. I have a project where I'm building SQL code via the C pre processor.

I would not recommend either of the above unless you like playing with highly abstracted code. It tends to act as a long lever, you push really really hard on the short end, move it a tiny bit and huge things happen.

Answer 5

If you download and use VisioModeler to capture your conceptual and/or logical design, it's pretty good at spitting out the DDL for an equivalent physical design for several RDBMSs.

http://www.microsoft.com/downloads/details.aspx?familyId=27fe6786-a439-4286-b8b6-7a9b84cfa709&displaylang=en

Or there are a number of other ERD type tools that accomplish roughly the same thing. You might try DBDesigner.

http://www.fabforce.net/downloads.php

Answer 6

Depending upon which language you use, you can insert a ORM layer in-between, such as (for instance) Doctrine PHP which should help you not have to write SQL directly. There are a number of great suggestions as far as the initial schema in other comments.

Answer 7

There are several problems I have run across in working with databases designed to use mulitple backends.

First is that data types are different and have differnt ways of handling data. This tends to be especially tricky when handling dates and large amounts of text.

The next thing I have noticed is that, because they try to use ANSII standard SQL, issues arise when a database doesn't fully support the standard. An even larger problem is the standard is often not the most efficient way to get data from a particular database. Every commercial database I've ever worked with that offers multiple backends is dead slow when you get a lot of records in the tables. ANSII standard also does not offer good ways to solve of the more complex problems and so you end up with convoluted workarounds.

Another approach is to used stored procs and write one for each database backend you support but with the same names across databases. This way you can take advantage of the performance tuning available for each database and differing database structures without having to change the user interface, but it is far harder to maintain because every change has to be written for every database. However it will probably be far faster for your users than your competition, so you might be able to change a premium price for the product. You will need to charge a premium though because of the extra cost of maintenance and the extra database specialists you will need on hand to performance tune for each type of database (a mysql specialist probably won't know how to best tune a SQL Server database and vice versa)

An alternative to consider is that you host the data and the users access it from the web. Then you only need to design and support the one database you choose. You will need a whole pile of servers and network and dba people to do this though as you will need to maintain basically 24 hour uptime for each client and they may require you to store their data on servers that do not contain any data from competitors. Not knowing the business you are developing software for, it is hard to say if this is a viable alternative.

The least expensive route is to require a particular backend. You may lose potential customers who don't want to buy a differnt backend though. If you go this way, I'd survey the potential customers to find databases they use right now, it might be that one is the de facto industry standard and you would lose very few potential customers by requiring it as the backend. Also if you can make the selling point that the product is significantly faster than the competition becasue you have a standard backend, you might be able to persuade them anyway. But if you go that route, you had better design a database that is blazingly fast.

Answer 8

Design a logical database that is SQL specific but SQL dialect independent. The design should include Tables, columns, domains and constraints. constraints include, but are not limited to, primary key constraints and foreign key constraints.

Domain specification will be tricky to define in terms that don't favor one SQL dialect over another.

Create a physical design for each back end that conforms to the logical design, and uses the features of the specific back end.

In the application, use lowest common denominator SQL wherever you can, without imposing too great a penalty in performance or code flexibility. Where necessary, code in back end specific methods. Hide these back end dependencies inside a few objects. Make most of the application DBMS agnostic.

Answer 9

Datanamic's DeZign for Databases supports database-independent modeling.

Answer 10

Mysql provide an export functionnality that let you export your schema into SQL Server compatible format.

As said before, use the simpliest type possible, and do not rely on standard SQL.