Reflecting Clients name on Projects everywhenre if the client name gets updated

[sjthetechguy]

Hi, lets say that I have a database table called clients and it has columns id, name, email

I have another table called Projects which has databse records and it fetches the record from clients table to sselect client.

After i have added the project record against the client. I can see that the Projects table has the data.

Now, later on if i go to the client manager and i change the spelling or email address. I dont see any change of spelling name of client or email on the projects table.

How to make it work, so that when i update something from a central place, everything everywhere gets updated?

[Barand]

Data like names should be stored in one place only in a database.

In this case, the only place the client name should be stored is in the "client" table. Other tables related to the client, such as "project" should contain the id of the id of the related client,

When you query the project table you then JOIN to the client table using the client id. This way, a change in one place is all that is required.

Databases should be correctly designed using a process of normalization - Google it.

[Barand]

Example data

TABLE: client                        TABLE: project
+----+-----------+----------+        +----+---------------+-----------+------------+
| id | firstname | lastname |        | id | project_name  | client_id | start_date |
+----+-----------+----------+        +----+---------------+-----------+------------+
|  1 | Scott     | Chegg    |        |  1 | Project Alpha |         4 | 2022-12-01 |
|  2 | Laura     | Norder   |        |  2 | Proect Beta   |         2 | 2023-01-15 |
|  3 | Tom       | DiCanari |        |  3 | Project Gamma |         4 | 2023-03-01 |
|  4 | S         | Tonin    |        |  4 | Project Delta |         1 | 2023-03-20 |
+----+-----------+----------+        +----+---------------+-----------+------------+

Query

SELECT project_name
     , start_date
     , CONCAT(c.firstname, ' ', c.lastname) as client
FROM project p 
     JOIN
     client c 
          ON p.client_id = c.id
ORDER BY client, start_date;

+---------------+------------+--------------+
| project_name  | start_date | client       |
+---------------+------------+--------------+
| Proect Beta   | 2023-01-15 | Laura Norder |
| Project Alpha | 2022-12-01 | S Tonin      |
| Project Gamma | 2023-03-01 | S Tonin      |
| Project Delta | 2023-03-20 | Scott Chegg  |
+---------------+------------+--------------+

Now change the first name of client 4 and re-query

UPDATE client
    SET firstname = 'Sarah'
    WHERE id = 4;

SELECT project_name
     , start_date
     , CONCAT(c.firstname, ' ', c.lastname) as client
FROM project p 
     JOIN
     client c 
          ON p.client_id = c.id
ORDER BY client, start_date;

+---------------+------------+--------------+
| project_name  | start_date | client       |
+---------------+------------+--------------+
| Proect Beta   | 2023-01-15 | Laura Norder |
| Project Alpha | 2022-12-01 | Sarah Tonin  |
| Project Gamma | 2023-03-01 | Sarah Tonin  |
| Project Delta | 2023-03-20 | Scott Chegg  |
+---------------+------------+--------------+