Advanced Database Design For a Relational DB w/ MySQL

There are 9 essentials:

1. Understand the database’s purpose (OLTP/OLAP?)
2. Get the right tool (MySQL Workbench)
3. Gather the requirements for the database (biz, tech, ops)
4. Standardize the naming convention
5. Enforce relationships (data integrity)
6. Use the appropriate data types (use a variety of data types)
7. Be conscientious when modeling the data (do what’s right!)
8. Include indexes when modeling
9. Document your work & keep track of changes (GitHub repo)

1. Poor design/planning
2. Ignoring normalization
3. Poor naming standards
4. Lack of documentation
5. One table to hold all domain values
6. Using identity columns as your ONLY key
7. Not using SQL facilities to protect data integrity
8. Trying to build generic objects (common reference table)
9. Lack of testing
10. Poor deployment workflow (lack of environments)

When you start your database design the first thing to analyze is the nature of the application you are designing for, is it Transactional or Analytical. You will find many developers by default applying normalization rules without thinking about the nature of the application and then later getting into performance and customization issues. As said, there are two kinds of applications: transaction based and analytical based, let’s understand what these types are.

Transactional: In this kind of application, your end user is more interested in CRUD, i.e., creating, reading, updating, and deleting records. The official name for such a kind of database is OLTP.

Analytical: In these kinds of applications your end user is more interested in analysis, reporting, forecasting, etc. These kinds of databases have a less number of inserts and updates. The main intention here is to fetch and analyze data as fast as possible. The official name for such a kind of database is OLAP.

If you think inserts, updates, and deletes are more prominent then go for a normalized table design, else create a flat denormalized database structure.

There is no reason to be inconsistent in your naming except sheer laziness.

Naming conventions might not appear important during the design. In reality, names provide the insight to understanding a model. They are an introduction and should be logical.

Inconsistent naming serves no purpose. It only frustrates developers who must access the data, administrators of the database, and modelers who must make changes in the future.

Weak naming conventions also lead to errors in development because the naming is not consistent.

Hand-in-hand with documentation, using a naming convention makes it in the future for someone to understand the model.

Generate the DDL scripts, store these in version control (e.g. GitHub). 

Other scripts that are also worth to be stored in version control: scripts to populate initial data (e.g. registry entries, lookup tables: country codes etc), export/import, migration, conversion between different applications & versions. 

If you have multiple scripting files, make a master script that will call those scripts with the right order dependencies. This self-maintenance approach cost efforts and error prone. Often the only reliable way to migrate is by drop the database and create the new one using the scripts from version control, this process can cost downtime.

Use a database tool (e.g. MySQL Workbench) that tracks the changes and generates migration scripts from diff operation

Indexes are important when considering queries on the data. When modeling, you should consider how the data will be queried. Take care not to over-index. Indexing revolves around query optimization.

Each table must have a primary key.

Natural key vs artificial key. 

Use defined sequence object if you need to generate sequence (e.g. to generate primary key during insert)

When deciding which field or fields to use as keys in a table, always consider the fields that users will be editing. It’s usually a bad idea to choose a user-editable field as a key.

Lack of database documentation is evil. Document your database design with ER schemas and instructions. Also write comment lines for your triggers, stored procedures and other scripts.

Provide documentation, describe all tables & relationships, DDL. The application programmers should be able to find documentations about any triggers, constraints & store procedures in your database.

Documentation is often an afterthought. When schedules are short, documentation gets ignored. Yet, this is technical debt with a high cost. Cutting corners during the development cycle will accrue costs in the future for database changes, problem identification, tracking bugs and for understanding the data model and nature of the data.

Learn why to avoid triggers.