Bad Practices

Bad Practices

1. Ignoring the Purpose of the Data

   • Cause
     • Designs that are flawed in their basics
   • How to prevent
     • Designs that are flawed in their basics

2. Poor Normalization

   • Cause
     • Bad performance
   • How to Prevent
     • Every database should, at least, be normalized to third normal form

3. Redundancy

   • Cause
     • increase of database size, data being prone to inconsistency, and decreases in the efficiency of the database, but—more importantly—it may lead to data corruption.
   • How to Prevent
     • Redundancy may seem necessary, it must be used only in very specific cases and be clearly documented in order to be taken into consideration in future developments.

4. Bad Referential Integrity (Constraints)

   • Cause
     • Making it susceptible to human error.
   • How to Prevent
     • Make use of Referential integrity

5. Not Taking Advantage of DB Engine Features

   • Development to an extremely uncertain path and surely to bugs and future problems.
   • Take Advantage of DB Engine Features

6. Composite Primary Keys

   • The best practices indicate that it is better to use as a foreign key a compound defined by the combination of two or more fields, however it must be borne in mind that if the composite primary key happens to have a million records this can generate performance problems.

7. Poor Indexing

   • Cause
     • Bad performance
     • Re design
   • How to Prevent
     • Take into account the inconveniences presented when choosing a different index of whole, since these are not as efficient
     • Having a table with no index in the columns that are used to consult it will lead, as we all know, to poor performance in the SELECT.
     • Having only one index in all the different columns of the primary key used to query the table, ordering these columns from the most used to the least can offer better performance in all CRUD operations than one index per column.

8. Poor Naming Conventions

   • How to Prevent
     • The table name must describe what entity it holds, and each column name must describe what piece of information it represents
     • Always make use of naming convention
   • Cause
     • Database becomes unreadable.