Clever Geek Handbook

Is a separate query good enough or is there some kind of join I can make?

Basically I have a small "invoicing" type system in which I have a MySQL table set up for individual invoices, invoice rows and items associated with each invoice, like:

tabular accounts:

fields: invoice_id | type | fees | comments | company

-

invoice_lines table:

fields: id | invoice_id | name | description | quantity

-

invoice_affected_items table

fields: id | invoice_id | item_id

I can have many account lines and many affected items per account ID. In this case, the best would be a join, or should I just join the account rows to the table and retrieve the affected items with a separate query?

Thank.

+3


source to share


3 answers


You can join, but this will expose a lot of extra data. If you attach lines to invoices, for example, you will be pulling the invoice multiple times - once for each line ...



So to answer your question. I would do this in separate queries.

+1


source


IMHO If the affected items have a one-to-one relationship with the invoice lines (via invoice_lines.id = invoice_affected_items.item_id) you can do one of two things

Or keep the current design and make separate requests



or redesign as follows

  • Delete tables completely invoice_affected_items

  • Add flag invoice_affected_items

    to tableinvoice_lines

  • Index invoice_lines

    by (invoice_affected_items, invoice_id) to see which invoices affected the items
  • Index invoice_lines

    by (invoice_affected_items, id) to see which invoice rows are affected items.
+2


source


If there is no relationship between invoice_lines and invoice_affected_items, I would make 2 separate requests.

0


source






More articles:


All Articles