Advanced Database Design For a Relational DB w/ MySQL
- 2 hours on-demand video
- 10 articles
- Full lifetime access
- Access on mobile and TV
- Certificate of Completion
Get your team access to 4,000+ top Udemy courses anytime, anywhere.Try Udemy for Business
- To Learn Database design Process
- Learn what are the steps to create a sound database design
- You will be able to design a relational database from scratch
There are 9 essentials:
- Understand the database’s purpose (OLTP/OLAP?)
- Get the right tool (MySQL Workbench)
- Gather the requirements for the database (biz, tech, ops)
- Standardize the naming convention
- Enforce relationships (data integrity)
- Use the appropriate data types (use a variety of data types)
- Be conscientious when modeling the data (do what’s right!)
- Include indexes when modeling
- Document your work & keep track of changes (GitHub repo)
- Poor design/planning
- Ignoring normalization
- Poor naming standards
- Lack of documentation
- One table to hold all domain values
- Using identity columns as your ONLY key
- Not using SQL facilities to protect data integrity
- Trying to build generic objects (common reference table)
- Lack of testing
- 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.
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.
- Basic MySQL knowledge
- This course is not for SQL beginners. It requires you to know SQL - we do not go in depths of the queries
Learn the basics elsewhere and get the advanced stuff here:
- OLTP vs. OLAP database design
- Reference tables
- Database deployment
- Naming conventions best practices
- And a lot more for those who want to get to the next level
If you want to improve your software development career, get into database admin or database design, or are just curious on this topic, this course is for you.
- Web Developers
- Database Designers
- Full-stack Software Engineers