How to integrate one database table into another?

Asked

Viewed 263 times

1

Example: A system that records customers, purchases and products, each in a different table. Having a purchase record that must own the fields:

  • Client: indicates which customer already registered (in another bank table) made the purchase.

  • Products: selects which product (which is also in another table) was sold to the customer.

In addition I should put other fields as date of sale etc, but this is not the case, because I want to know how I do this integration between tables? Take the record from another table and associate it to a field from a record from another table. I’ve tried researching it but I couldn’t find it, if you give me an example of how it would work or terms I should search I would appreciate it.

I ask you not to put too much code in the language of the database, because I do not understand any of this, the banks I create are totally visual, I mean, created automatically by Visual Studio.

Details: It should work as follows, when wanting to register a purchase, the user would inform the date of sale (that part I know how it does), the customer who bought that (this customer has a record in another table so you must search for and associate the customer’s name with your data stored in the other table) and finally the product sold (which also has record in another table)

database

1 answer

1


Integration between tables is usually done by foreign keys. A foreign key relates a primary key of a table to the primary key of another table. This ensures the referential integrity between tables.

For your example, a purchase should relate products to a customer. The ideal would be to have an associative table between products and a purchase, having an additional field, the quantity. That is, this associative table must have, in addition to its primary key, two foreign keys, one pointing to a purchase and the other pointing to the product that belongs to a purchase.

  • Thanks, I was able to understand a little and know what I should research now to do this. In my case it would be the purchase table with two foreign keys, one pointing to customer and the other to product (the quantity is not so necessary in the purchase table, when a product element is assigned in the purchase table I put to decrease a quantity of that product in the product table). So I should turn the Name field into the customer table and product as the primary key right? Kind of tricky this huh kk I’ll do a search on youtube

  • Well, I did it here at Visual Studio, but I couldn’t distinguish it from a normal table, the Client field accepted a name that I didn’t have in my client table, the same happened with the product..

  • The field Nome is not a good alternative as a primary key for being a varchar, possibly. Primary and foreign keys cannot be easy to modify. As much as you say it will not be modified, there is also the problem of varchar be more expensive in database operation cost, to check value, select, insert, update... I suggest asking another question with the new question.

Browser other questions tagged database

You are not signed in. Login or sign up in order to post.