Question:
I'm learning to work with MySQL and I'm seeing some examples of how to work with more than one table, as shown below:
Id Nome Faculdade_id
101 Amanda 11
102 Bianca 12
103 Carla 13
104 Daniela 12
105 Edna 11
id Faculdade Estado
11 UERJ RJ
12 UESP SP
13 UNEB BA
The objective of the example is to learn how to use more than one table, but there was a doubt, in practice what is the advantage of creating two tables instead of simply creating other columns?
In the example I put I imagine that there is no more advantage if the tables are several columns and hundreds of rows, would it really be faster to use two tables instead of including more columns?
Answer:
When creating a second table you can divide information, imagine that you have all the data pertinent to a person, about 20 metadata for example and one of these metadata refers to the college that this person attended, keep all the college information in the table of people is not semantically correct and it makes the database bring more information if no filter is applied. If there are 15 metadata referring to the faculty, the table of people can be very large and it will be more difficult to work with it.