Understanding a database

Before the structure: why a base becomes necessary

Imagine a catalog whose stock is 8 in the store file and 11 in the online store. One order references an absent customer, while two sales reports result in different totals.

None of these problems are solved by first learning a keyword SQL. You need a shared source where information has a known place, where relationships are verifiable and where rules prevent impossible states.

A spreadsheet remains very useful for a one-off analysis or a small data set. A database becomes relevant when several processes must read and modify linked data with the same rules.

• Duplicate information: two versions may differ.
• Inconsistent stock: the decision depends on the open file.
• Order without customer: the business relationship is broken.
• Non-reproducible report: the calculation or source is not shared.

A table organizes the same type of object

A relational database stores information in tables. A table clients contains customers; a table commandes contains orders.

Each table has a defined structure. This prevents a date, amount or identifier from being interpreted differently depending on who opens the file.

• A line represents a record.
• A column represents a property.
• A type specifies the authorized values: text, number, date or boolean.

The primary key identifies a row

Two clients can have the same name. A primary key like id_client gives each row a unique and stable identifier.

A command can then store id_client as a foreign key. This reference connects the two tables without copying all the customer information.

clients
- id_client  INTEGER  PRIMARY KEY
- nom        VARCHAR
- ville      VARCHAR

commandes
- id_commande    INTEGER  PRIMARY KEY
- id_client      INTEGER  REFERENCES clients(id_client)
- montant        DECIMAL

Relational database or Excel sheet?

A spreadsheet is excellent for quickly exploring a small dataset. A relational database is designed to maintain rules, connect several sets and serve several uses without multiplying copies.

SQL then allows you to ask a specific question to this database: which customers live in Tunis, which orders exceed 100 TND or which product achieves the most sales?

• Excel favors the visual manipulation of a sheet.
• A foundation emphasizes consistency and relationships.
• SQL describes the desired result without changing the default data.

Read a base like a professional

Before writing a query, an analyst seeks to understand what a row represents, which columns are required and how the tables relate. This reading of the model avoids producing a result that is technically valid but false on a business level.

Always ask who creates the data, when and according to what rules. An order date does not have the same meaning as a payment date; an amount excluding taxes should not be compared directly to an amount including all taxes.

In a real project, write down important definitions in a data dictionary. A few lines of shared documentation reduce ambiguity and make analyzes reproducible.

• Formulate what a line represents.
• Locate the optional keys and columns.
• Validate units, dates and statuses before calculating.

Check your understanding

In a produits table, which column would be the best primary key: produit, prix, or id_produit?