An OLTP (On-Line Transaction Processing) systems are transaction based systems which provide the source data and are characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). An OLAP (On-Line Analytical Processing) systems are analytical system which are used to analyze the data and are characterized by relatively low volume of transactions.
OLTP systems maintain data integrity and they also provide fast query processing in environments having multiple users accessing the system. Transactions are measured using under one second times transaction, so efficiency of your OLTP system is very important.
In this article by Hans Michiels we learn his take on the top 10 design mistakes seen in OLTP production systems.
- Don’t have referential integrity enforced
- Foreign key columns having a totally different name from the primary key they refer to
- Foreign key columns missing “Id”, “Key” or “Guid” in the name
- Foreign key columns meaning more than one thing
- Accessing the datamodel through low-performance all-in-one views, protecting base tables from direct access
- Oversized column lengths
- Oversized INT Datatypes
- Lack of unique indexes for business or natural keys
- Guids stored as (n)varchar(36)
- Where’s the DBA?