Field value related to another field of another table?

Question

In MS Access I have two tables: tbCursos and tbPeople
tbPeople contains: ID, Nome, CursoID, CursoNome;
tbCursos contains: ID, Nome;

It is possible to make the field CursoNome table tbPeople have the same value as the course name of the other table according to the value of the CursoID?

tbPeople:

tbCursos:

In the two images above, CursoNome has as value 'Right' because the value of CursoID is 2.

The question is, how to do so when changing the value of the field CursoID, the field CursoNome change as the contents of the other table?

Answer 1

A database is composed of several tables, such as: Customers, Products, Orders, Order Details, etc. Although the information is separate in each of the Tables, in practice there must be relationships between the tables. For example: An Order is placed by a Customer and in this Order there may be several items, items that are recorded in the Order Details table. In addition each Order has a unique number (Order Code), but the same Customer can place several orders and so on.

In a database, we need to somehow represent these real-life relationships in terms of the tables and their attributes. This is possible with the use of "Relationships between tables", which can be of three types:

• One to One
• One to Several
• Several to Several

Type One to One Relationship:

This relationship exists when the related fields are both of the Primary Key type, in their respective tables. Each of the fields does not have repeated values. In practice there are few situations where we will use a relationship of this type. An example could be the following: Imagine a school with a Student Register in the Students table, of these only a small part participates in the School Band. For Database design reasons, we can create a Second Table "Band Students", which relates to the Students table through a One to One relationship. Each student is only registered once in the Students Table and only once in the Students Table. We could use the Student Enrollment Field as the Field that relates the two Tables.

Important: The field that relates two tables must be part, having been defined, in the structure of the two tables.

In the table Students of the Band we could put only the Enrollment Number of the student, in addition to the information about the Instrument he plays, time of band, etc. When it was necessary to seek the information such as name, address, etc., these can be recovered through the existing relationship between the two tables, thus avoiding that the same information (Name, Address, etc.) has to be duplicated in the two tables, including increasing the probability of typing errors.

Type One to Multiple Relationship:

This is, of course, the most common type of relationship between two tables. One of the tables (side one of the relationship) has a field that is the Primary Key and the other table (side several) relates through a field whose related values can be repeated several times.

Consider the example between the Customers and Orders table. Each Client is only registered once in the Clients table (so the Customer Code field in the Clients table is a primary key, indicating that two customers with the same code cannot be registered), so the Customers table will be the one side of the relationship. At the same time each customer can place several orders, so that the same Customer Code can appear several times in the Orders table: as many times as the orders that the Customer has made. That is why we have a One to Several relationship between the Customers and Orders table, through the Customer Code field, indicating that the same Customer can place several (several) orders.

Miscellaneous to Various Type Relationship:

This type of relationship would "happen" in a situation where on both sides of the relationship values could repeat. We will consider the case between Products and Orders. I may have Multiple Orders in which a particular product appears, and several Products may appear in the same Order. This is a situation where we have a Multiple-to-Multiple-Type Relationship.

In practice it is not possible to implement such a relationship due to a number of problems that would be introduced in the database model. For example, in the Orders table we would have to repeat the Order Number, Customer Name, Employee Name, Order Date, etc for each Order item.

Here’s a good PDF for you to use some examples!

Answer 2

I don’t know if it helps much, but the situation you described, in real database environment (Postgre) for example, I would make a trigger (trigger), after insert or update on the table tbPessoas and in that trigger would create a specific function for this field (IF old.campo <> new.campo) would do an update seeking the related course.

In Access, I don’t know if there’s any way to create "Trigger/triggers," but search for it if you have how I believe a Rigger solves your problem. Example of Trigger in postgre: Trigger example

I hope I’ve helped!

Answer 3

You shouldn’t do that. If it was a Nosql base, it would make sense, but in a relational bank, you’re certainly doing something wrong.

If you want the list of people and what courses you are applying, use JOIN:

SELECT *
  FROM tbPessoas P
  JOIN tbCurso C ON C.ID = P.CursoID

This will result in a consultation the way you expect.

And remove the column CursoNome from tbPessoa table, this is data redundancy, therefore, bad practice. Ah, and also removes the tb in the name prefix of the tablets. Not necessary and also another bad practice.