How to use "joined table" in MySQL
Today, I would like to write down one basic approach on handling the "joined table".
What is "joined table"?
My understanding of "joined table" is that you "combine" two tables into one table.
We use JOIN to join two tables together.
First check this article to understand how JOIN is used in MySQL, we will use this example in the following explaination.
Ok, now the question is, why we use this "joined table"?
Because according to the article, if we wanna get the item from "goods" we can just access to "goods" table, and access to "cate" table to get "cate" table items. Then why we have to create another table (*actually "join" doesn't create any new tables in DB. JOIN is more like "you join one table with another so you can virtually create a new "joined table" to access any items you want from both tables") to access?
My answer is, DB access will be a cost everytime when program tries to start a new query, for example INSERT, UPDATE and SELECT etc.
So, now if we use "unjoined way" to create a webpage which shows "goods" and "cate", it means we have to do SELECT twice, one for "goods" and one for "cate".
This seems ok in our case because we only have less than ten items in each table, but how about if data volume is 100,000 or 100,000,000?
This is why we use JOIN to "combine" two tables together so we can use SELECT for only one time.
And here comes the tricky thing.
After JOIN, how we get data?
We only have "goods" entity(repository) and "cate" entity(repository).
The answer is we must create a new entity to store this "new table".
In our case, we have to create a new class, we name it "GoodsAndCate". This class field should have every items we want to use from both "goods" and "cate".
When we pass this variable to view, we could use "goodsAndCate.getGoodsName", "goodsAndCate.getCateId" to display these information on the webpage.
