Data modeling is the process of creating a model for the data you want to store in a database. To design a database you need to understand:

1. The business requirements

2. Build a conceptual model of the business

3. Build a logical model

4. Build a physical model

Business requirement: a business requirement is a set of specifications or functionalities that a software system or application must meet to satisfy the needs of the business or organization. These requirements are typically defined through a process of gathering input from stakeholders and analyzing business processes to identify specific needs.

Once these requirements have been identified, they are documented in a formal requirements document or user story that serves as a blueprint for software development. This document outlines the functional and non-functional requirements that the software must meet, including specific features, performance characteristics, security requirements, and any other constraints or specifications that must be met.

The development team then uses these requirements to guide the design, development, and testing of the software system or application. This ensures that the final product meets the needs of the business or organization and delivers the expected value. Additionally, business requirements are used to measure the success of the software solution and evaluate its impact on the business or organization. For example:

We want to build a job search app. We need a Profile model that collects the user's information. A job search app would need your name, email, nationality, and profession. So if we go on to include a section for a middle name, maiden name, hobbies, etc all these are not necessary for a job search app. It would only complicate things and we end up storing things that are not necessary. As Mosh would say; Don’t store the data you don’t need. Solve today’s problems, not tomorrow’s future problems that may never happen.

Conceptual model: this represents entities, attributes, and their relationships. It is the highest level and least detailed.

For example, in the diagram above, A book is an entity. Title, description, etc is an attribute. We are concerned about gathering as much data as possible according to the business requirements.

Logical model: this is the second level. It represents entities, attributes, data types, and their relationships. We can give a unique identifier id also known as the primary key pk a datatype of int, we give the title a datatype of varchar(250), 250 is the word limit, and description a datatype of text because a description can be a long text.

Physical model: it's time to choose a database and design the model. Below is an example of what it looks like in MySQL Workbench.

AI: means auto-incerement

NN: means not null. If the box is checked it doesn't allow null values.

UQ: means unique. It wouldn't allow duplicates.

PK: means primary key. You can set a pk for every entity. It uniquely identifies an object in the database. You can also set the username to pk but the idea behind pk is that it should be unique e.g you can use a uuid as a pk to identify a model in the record.

Relationships

A relationship occurs when an entity is dependent on another entity. The entities share something in common. For example, there is a relationship between book and category, user and profiles, cart and cart item, post and comments etc

We have 3 relationships:

1. One-to-one relationship

2. One-to-many relationship

3. Many-to-many relationship

One-to-one relationship: this relationship can exist between users and profiles. For example, the user model contains attributes like first name, last name, email, and username. We need to extend the user model to contain other information so we form a one-to-one relationship between the user and the profile. The profile model can contain image, gender, hobbies, etc

One-to-many relationship: some call it a many-to-one relationship. We also call it a foreign key fk. In this relationship, we have a parent model and a child model. The fk will be in the child model. This relationship exists between the project and the review. For example, a project can have several reviews, several reviews are to one project.

project 1 ---> * review review * ---> project 1

The first sample is a one-to-many relationship. The second sample is a many-to-one relationship.

This is a bit tricky and it gets confusing to me even now but I decided to develop a way to make it less confusing. So which is the parent and child model? The project is the parent and the review is the child. The project can exist without a review but a review is dependent on the project. Well, these can be reversed if the role is switched depending on the project requirement. Remember we said that the fk will be in the child model. So we add the Project model to the Review model using an fk. Below is an example of a database model in Django. I know you are still confused and sincerely this confuses me all the time. That is why I am going to show you a little trick to clear up your confusion.

class Project(BaseModel):
    # ...
    title = models.CharField(max_length=200)
    description = models.TextField(null=True, blank=True)

class Review(BaseModel):
    # ...
    project = models.ForeignKey(Project, on_delete=models.CASCADE, related_name='review')
    body = models.TextField(null=True, blank=True)

This is the admin page on my browser. Django provides this interface so we can manage our data, it works just like a database. So let's check the Review section. We can see a dropdown menu to choose as many books as possible.

If the role is reversed the dropdown menu would be in the Project section. Now you get my point. If you can always think of it that way you won't ever get confused. Whenever things get confusing I like to look for a simple way to help me remember them. I just taught you a method I came up with. This is to tell you that you can always improvise a method to clear your confusion if a concept keeps getting confusing to you.

Many-to-many relationship: this type of relationship exists between Project and tags. Note that there are so many examples depending on your business requirement. So there is no right or wrong way of doing it, any of the relationships could be switched. So a project can have so many tags and a tag can refer to several projects. So what relationship exists? It is a many-to-many relationship. Now the question is where do we put the many-to-many field? It could be in the tags or Project model but we would prefer it to be in a Project model so that a tag only contains the name attribute. Another reason is when filling out a project form we can easily add tags.

Technical terms

• pk or id: this is a unique identifier. Every table should have a primary key. We can use it to look up data in the database. For example:

•   SELECT *
    FROM customers
    WHERE customer_id = 1
  

  We queried the database to return a customer of id=1, so it returns every data about the customer.

• Indexes: these are used to speed up our queries. So instead of looking up the entire record it just searches the database by the created index.

    CREATE INDEX idx_state ON customers (state)
    EXPLAIN SELECT ... FROM ... WHERE