Do you understand relational database design?

The problems of a poor database design

If a database lacks proper structure and organization, many problems can arise. These include:

• Data Duplication - If the employee details, like home address, are stored separately in the HR and payroll departments, an update in one place may not reflect in the other, leading to inconsistencies
• Update Anomalies - If a customer's phone number is stored in multiple places in the database, a change in contact information might not be updated everywhere, causing confusion when attempting to reach them
• Insertion Anomalies - If a database requires a vendor's details when adding a new product, it would be difficult to add a new product if the vendor information isn't available at the time of entry
• Deletion Anomalies - If a customer record is deleted from the database, it might unintentionally delete all associated order records, erasing valuable sales data

The solution

Solving these problems is hard. Luckily, there are some tried and tested tools that help resolve these problems including:

• Database Normalization
• Entity Relationship Diagrams (ERDs)

Database normalization and ERDs are the core tools needed to perform good relational database design. If these concepts are misapplied or are not used at all, there can be disastrous consequences with far-reaching effects. Once you start putting data into a table, it becomes significantly harder to fix since you now have a data migration problem. That's why getting database design right the first time is vital.