Top 10 Design Mistakes In Production OLTP Systems

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.

  1. Don’t have referential integrity enforced
  2. Foreign key columns having a totally different name from the primary key they refer to
  3. Foreign key columns missing “Id”, “Key” or “Guid” in the name
  4. Foreign key columns meaning more than one thing
  5. Accessing the datamodel through low-performance all-in-one views, protecting base tables from direct access
  6. Oversized column lengths
  7. Oversized INT Datatypes
  8. Lack of unique indexes for business or natural keys
  9. Guids stored as (n)varchar(36)
  10. Where’s the DBA?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.