在MYSQL中操作外键输出

Question

I have a table connected to another table through foreign key;

1. First Table is the brand; it has a brand_id and brand_name fields
2. Second Table is the Product Line; it has a Line_id and line_name fields
3. Third Table is the Lines Offered; it has a id, Brand (which is a foreign key from the first table), and Line_name (which is a foreign key from the second table.

If i will look into the third table on mysql the fields contains the id numbers of the foreign keys.

My question is this, is it possible that the stored value will be the name itself and not the ID? or Is it possible to add a field on my third table named it as Brand_name which is a VARCHAR that will display the exact brand name from the first table. Example the values of my third table would be '1','3','The Brand Name of brand with id no. 3','25'; - If yes i dont have any idea how to do this.

Answer 1

Nothing is preventing you from doing this. You would simply add a brand_name field to the "third table"... The question begging to be asked is: why do you want to do this?

brands
_________________________
| brand_id | brand_name |
|     1    |   brand1   |
|     2    |   brand2   |
|     3    |   brand3   |
|     4    |   brand4   |
|     5    |   brand5   |
-------------------------


lines
_________________________
| line_id  | line_name  |
|     1    |   line1    |
|     2    |   line2    |
|     3    |   line3    |
|     4    |   line4    |
|     5    |   line5    |
-------------------------


linked
_________________________________________________
|    id    |  line_id   | brand_id | brand_name |
|     1    |     5      |     1    |   brand1   |
|     2    |     5      |     2    |   brand2   |
|     3    |     4      |     2    |   brand2   |
|     4    |     4      |     3    |   brand3   |
|     5    |     4      |     3    |   brand3   |
|     6    |     3      |     4    |   brand4   |
|     7    |     3      |     4    |   brand4   |
|     8    |     2      |     4    |   brand4   |
|     9    |     2      |     5    |   brand5   |
|     10   |     1      |     5    |   brand5   |
------------------------------------------------

That's your proposed setup. Now if we:

SELECT brand_id, brand_name FROM linked WHERE line_id = 4;

We would get:

_________________________
| brand_id | brand_name |
|     2    |   brand2   |
|     3    |   brand3   |
|     3    |   brand3   |
-------------------------

But the same could be achieved without duplicate data (in large databases it's pretty unreasonable to have duplicate data like that), and without needing to update BOTH the linked and brand tables every time the brand name changes by using:

SELECT linked.brand_id, brands.brand_name 
FROM brands, linked
WHERE linked.line_id = 4 AND brands.brand_id = linked.brand_id;

_________________________
| brand_id | brand_name |
|     2    |   brand2   |
|     3    |   brand3   |
|     3    |   brand3   |
-------------------------

That answer didn't need to be that long. I was having fun making tables.