No matter the web or mobile app you're building, the specific DMS you choose, or client, there will always be a few foundational steps when beginning to build your own SQL database (DB). Following these 4 steps below will allow you to accurately tailor your database to the exact needs of your client or app.

Your Schema Design defines how you build your SQL database. To do so, start by beginning to ask basic questions about the project to develop the necessary understanding and background information required.

Now, begin with step 1...

Step 1: Break your understanding

You want to gather as much information as possible from your client or about your app to discover the hidden information, assumptions, and background information about the project. A great way to continue doing so is to try to break your initial understanding or design of the DB and project. In other words, try to give your understanding of the project a stress test. Doing so will drastically cut down on having to make changes to your DB later on when it may be costlier to do so.

For example: Imagine you want to build a simple DB to store data for a local car dealership. You know initially that it needs to store all the cars the dealer holds, and general buyer information.

Now let's try to break our understanding and assumptions - is there only one car dealership? Are the conditions of each car the same (used, new)? Additionally, what about the possibility of one buyer for multiple cars? In trying to break your DB, or understanding, you will find edge cases or assumptions you or your client have made that will be relevant to the tables you choose, the attributes/columns, the relationships you create (see below), and overall designing your DB well in an organized and performant way.

Step 2: Define the schema

Next, we'll want to define the schema of our simple DB. To do so, start identifying the main objects of your project or system, as these main objects will roughly be your tables. You can think of objects as the physical items of the project if that may help.

Continuing from the previous example, our local car dealership is our project/system, and probably should include cars, buyers, and perhaps prices as our main objects. Hence, this may more sensibly include make, model, year, dealer cost, and sticker price for the cars, and things like name, address, phone number, and perhaps salary and credit score, from the buyers, etc. We could now begin translating cars and buyers into our 2 tables, with a rough sketch of their attributes/columns simplified below. We also at this moment should consider data types each attribute should have:

Cars -> Make, Model, Year, DealerCost, StickerPrice

Buyers -> Name, Address, PhoneNumber, Salary, CreditScore

Step 3: Define relationships

Before we finalize our tables and their attributes, we then must define the relationship between our tables in our simple DB design. This will typically mean adding a foreign key attribute/column to a table to implement the relationship.

Our relationships can be 1 of 3, either one-to-one, one-to-many, or many-to-many relationships.

One-to-one relationship: Every single row of Table 1 can be associated with exactly 1 single row of Table 2, hence "one-to-one".

One-to-many relationship: Every single row of Table 1 can be associated with many rows in Table 2, hence "one-to-many".

Many-to-many relationship: Many rows in Table 1 can be associated with many rows in Table 2, hence "many-to-many".

What kind of relationship do you think Cars and Buyers should have? Naturally, in most cases, you would expect a one-to-one relationship, where there's 1 buyer of 1 car (or vice versa, ie 1 car to 1 buyer). However, we can easily imagine a one-to-many relationship from Buyers to Cars, ie 1 buyer to multiple cars, as a possibility. Let's implement a one-to-many to cover every reasonable possibility in our schema:

You can see a few added attributes, namely the primary key for each table in bold (CarID and BuyerID). But we also now have a second CarID attribute, but in the Buyers table (in red). This is our foreign key, and implements our one-to-many relationship - it enables Buyers table to link back to Cars table, ie a buyer linking back to multiple rows/records of cars.

(As a side note, it may help to use ER diagrams to graphically lay out the relationships above)

Step 4: Walk through common actions

And finally, now we can walk through common actions and queries we may perform, and understand how to store and retrieve the relevant data. Walking through these actions then will further modify and eventually finalize the schema design we have created, where we may find we need to add more tables and columns, etc.

Summary

Note that this is largely for small DBs. If we wanted to design for a large DB, we would want to consider denormalizing as well, since joins are costly. Otherwise, here are the 4 steps in sum:

1. Break your understanding - Discover all the hidden information, assumptions, and background information about the project by stress testing your understanding.

2. Define the schema - Define the objects and their attributes in the system/project to make rough drafts of your DB's tables.

3. Define relationships - What are the relationships between the tables; one-to-one, one-to-many, or many-to-many?

4. Walk through common actions — Finally, walk through common actions and queries, and understand how to store and retrieve the relevant data.