Menu
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?
277
Database-agnostic is a term describing the capacity of software to function with any vendor's database management system (DBMS). In information technology (IT), agnostic refers to the ability of something – such as software or hardware – to work with various systems, rather than being customized for a single system.
Entity Framework therefore simplifies the development process and greatly aids in improving productivity while working with a database. Entity Framework does so via a model that serves as a database abstraction. However, this technology is not database agnostic.
The concept of 'platform agnostic' refers to a set of specific design attributes and philosophies normally associated to software products. An agnostic type of software would in fact be free from any ties to a specific platform or system and run equally well across more than one platform.
Today, MySQL is one of the most popular and widely used SQL databases. It is also one of the most used databases in Web Applications. Some of the world's largest Web-Scale applications (e.g., Facebook, Uber) uses MySQL.
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.
178
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
