Databases
StackOverflow2013
Postsdbo
POMySQL best practice to link tables together so you can get all data with one query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL best practice to link tables together so you can get all data with one query
    primarykey
    data
    text
    <p>I slept in school on database lesson and it sounded like nonsense anyway what they did in Microsoft Access. In MySQL everything is nice and clear. However i kind of puzzled by how to link tables together.</p> <p>I currently use this style</p> <p>primary table <strong>users</strong> primary key <strong>user_id</strong> other keys irrelevant</p> <p>table <strong>documents</strong> primary key <strong>document_id</strong> second column <strong>user_id</strong> (from user table)</p> <p>table <strong>schedule</strong> primary key <strong>document_id</strong> second column <strong>user_id</strong> (from user table)</p> <p>table <strong>interview</strong> primary key <strong>document_id</strong> second column <strong>user_id</strong> (from user table)</p> <p>and so on 20 more tables that connect between each other by user_id.</p> <p>and in pages i do one query where i join all tables. sometimes 10 tables or 20 tables join like below without any speed problem takes on average about 1 second for query to run. Tip for speed - to have index in other tables based for the column you use in LEFT JOIN criteria or be numeric.</p> <pre><code>SELECT users.first_name, documents.document_date, interview.interview_result, ... FROM users LEFT JOIN documents ON documents.user_id = users.user_id LEFT JOIN interview ON documents.user_id = users.user_id ... LIMIT 0, 20 </code></pre> <p>I know some people code like 5 different queries either sequential (one query selects data used in another query e.g. with loop then data from second query used in third query condition trough loop or smth) or independently select data from 5 tables and then in PHP join it all together in array based on user id. </p> <p>Also inside tables i have several records per user e.g. in "documents" i have several records per user for each document we need from him they differ by "document_type" field.</p> <p>However i had to do a page where i get all data for user from each table so query was not too practical and slow as hell to execute.</p> <pre><code>SELECT users.first_name, doc_resume.document_date AS resume_date, doc_passport.document_date AS passport_date, doc_photo.document_date AS photo_date, interview.interview_result, ... FROM users LEFT JOIN documents AS doc_resume ON doc_resume.user_id = users.user_id WHERE doc_resume.document_type = 1 LEFT JOIN documents AS doc_passport ON doc_passport.user_id = users.user_id WHERE doc_passport.document_type = 2 LEFT JOIN documents AS doc_photo ON doc_photo.user_id = users.user_id WHERE doc_photo.document_type = 3 LEFT JOIN interview ON documents.user_id = users.user_id ... LIMIT 0, 20 </code></pre> <p>Now i might need to design system where i have to get all data for user all the time, so obviously this approach of selecting with one query is not the best in this situation.</p> <p>NOW THE QUESTION: </p> <p>I rather have to select independently and then join in PHP or there's way to organize database so i can do it all in one query?</p> <p>I was thinking maybe if i DON'T put <strong>user_id</strong> in each table and rather have one table called "link_table" where i have primary key "link_id" (not used), second column <strong>user_id</strong>, third column <strong>document_id</strong> (key from documents table) fourth column <strong>interview_id</strong> (key from interview table)</p> <p>But then after writing this question i realized that in <strong>link table</strong> i will still have many records per each user e.g. for each record in <strong>documents</strong> i will have record <strong>link_table</strong> in link table, so it actually does not solve anything. or am i wrong.</p> <p>Edit: <strong>Actually i wanted to know not about speed, but rather about should i use dedicated table for linking tables together or not is there any other advantages/disadvantages than speed?</strong></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPetja Zaichikov
    1. USPetja Zaichikov
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POMySQL best practice to link tables together so you can get all data with one query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL best practice to link tables together so you can get all data with one query
      1. This table or related slice is empty.
      1. VTDownMod
    1. COHow did you come to the conclusion that the query was the slow part of your request?
      singulars
      1. POMySQL best practice to link tables together so you can get all data with one query
      1. USJustin Wood
    2. CO"I rather have to select independently and then join in PHP" No, horrible idea.
      singulars
      1. POMySQL best practice to link tables together so you can get all data with one query
      1. USMike Purcell
    3. COWhat having a link table solves is it allows you to link documents to things other than users. Or maybe one document linked to 50 users without requiring duplicated data.
      singulars
      1. POMySQL best practice to link tables together so you can get all data with one query
      1. USKevin B
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload