Relational Database Design

Ideally when you design the Database it is usually best when you do it with others who know more than you so that you understand the unique intricacies when you work with the design and the relationships.

It is necessary to make sure that we have data that is easily manageable using relations and foreign keys.

Not all the queries that return the same results have the same performance, some queries can do a lot of table scans while the others can do much less, this can be done by asking the database for Query performance

Never use SELECT * statements, always define what column you’d like to retrieve for performance reasons

SELECT DISTINCT model FROM racing;
%% instead of %%
SELECT * from racing;

Over the years the Database Scalability of applications has changed by the definition from a few hundred to a few thousand then millions and so forth, As time progressed these Databases had to up their game and implement some of the NoSQL Database features.

All SQL Databases are built on the ACID philosophy while the nosql is built on the BASE philosophy

Design Process

The standard workflow for designing a relational database:

graph LR
    A[Requirements Analysis] --> B["Conceptual Model ([[Entity-Relationship Diagram (ERD)|ERD]] / [[UML Class Diagram]])"]
    B --> C["Logical Model ([[Normalization]])"]
    C --> D[Physical Model]
    D --> E[Implementation]
    E --> F[Testing & Tuning]

1. Requirements Analysis — Identify entities, relationships, use cases
2. Conceptual Modeling — Draw ERDs or UML class diagrams to capture the domain
3. Logical Modeling — Apply functional dependency analysis, normalize to 3NF/BCNF
4. Physical Design — Choose data types, create indexes, define constraints
5. Implementation — Write DDL, populate data, test with Explain Analyze