Web_Dev_Grad

When creating any software application, one of the most important foundational steps is designing the database. A well-designed database ensures your app will be scalable, efficient, and easy to maintain. Poorly structured data, on the other hand, can lead to bugs, performance issues, and a lot of unnecessary headaches down the road. Before writing a single line of SQL or creating tables in your database, it's crucial to create a data model. This process culminates in an ERD that visualises your application's data entities, their attributes, and how they relate to each other.

The benefits of using a data model include:

• A clear blueprint for your application's structure.
• Reduced errors when building or querying the database.
• Easier collaboration between developers, designers, and stakeholders.
• A smoother transition from planning to implementation.

Although it can be tempting to build your database "on the fly," especially during rapid development, this can lead to major complications such as:

• Changing the schema later is difficult, especially when your application is already using live data.
• Refactoring table structures often involves downtime, data migration, or even data loss.
• Bugs and performance issues multiply when your structure isn't carefully planned.

ERDs are used to visualise your data, they show entities (boxes), their attributes (fields inside boxes) and their relationships (lines connecting the boxes). The 3 stages to create your ERDs are:

1. Conceptual Model
   • Focus: What data do we need?
   • Map out all entities, attributes, and relationships, without worrying about how or where it will be stored.
   • This is a high-level, abstract representation.
2. Logical Model
   • Focus: How will we structure this data in a relational database?
   • Choose which entities and relationships will actually become tables.
   • Define attributes as columns.
   • Set primary keys (PKs) and foreign keys (FKs) to manage relationships.
3. Physical Model
   • Focus: How will the data be stored in the actual system?
   • Decide which Database Management System (DBMS) (e.g., PostgreSQL, MySQL, MongoDB) you'll use.
   • Consider indexes, storage engines, and performance tuning for your chosen system.

As you design your logical model, one of the most important techniques is normalisation. The goal of normalisation is to avoid data duplication and ensure data consistency which can be done through relationships and the use of Foreign Keys.

Here are two examples of relational database ERDs that I have used in my projects. I used the free tier of LucidChart to create them using the template of "Database ER diagram (crow's foot)".