MYSQL summary tables for Web App advice

I have a database where i have the data in a number of tables with relationships for example

TABLE Cars (stock)
---------------------    
Model   colourid  Doors
----------------------
xyz       0       2
xyz       1       4

TABLE Colour

Colourid  Name
---------------------
0         Red
1         Green

I need to produce several regular summaries for example a summery in the format.

         | colour               |  Num Doors
Model    | red   green   blue   |  2   4   5   6
---------|----------------------|------------------
XYZ      |  1     2      3      |  4   5   3   5    <<< Numbers in stock

UPDATE - "a car can have an arrangement of doors for example 2 door cars or cars with 4 doors. In the summary it shows the number of cars in stock with each door configuration for a particular model eg there are 4 cars of xyz with 2 doors. Please bare in mind that this is only an example, cars may not be the best example its all i could come up at the time"

Unfortunately rearranging tables may make them better for summaries but not for the day to day operations.

I can think of several ways to produce theses summary's eg/ multiple SQL queries and put the table together at presentation level, SQL level UNION with multiple queries, VIEWS开发者_Python百科 with multiple nested queries or lastly cron jobs or trigger code to produce data in a summary table with data arranged suitable for summary queries and reporting.

I wonder if anyone could please give me some guidance considering these methods aren't very efficient, made worse in a multi user environments and where that regular summaries may be required.

---

I think you need a data warehousing solution - basically build a new schema just for reporting purpose and populate these tables periodically.

There can be several update mechanisms for the summary tables -

1. Background job scheduled to do this periodically. This is best if up-to-date information is not needed.

2. Update the summary table using triggers on the main transaction tables. This could get somewhat complicated, but it might be warrantied if you need up-to-date information.

3. Update the report tables whenever a report is drawn just before showing the report. You can use some anchor values to ensure that you are not recalculating entire report too frequently, just consider the new rows or newly updated rows after the last time the report was drawn.

Only problem is that you will need to alter the table several times whenever new values get added in the pivoted columns.

---

Just a small variation on Roopesh's answer

Depending on the size of the database, available server resources, how often you would run these reports and particularly if you can not allow to have stale reports you might do the conceptually the same as above, but not using real tables, but views

Here are two links that should get you started

• Pivot in MySQL
• MySQL Wizardry

Notes:

• you don't have to run any DDL (you can even skip CREATE VIEW and use straight dynamic SQL) as compared to having materialized results
• the complexity is comparable, but little lower (adding new value in materialized scenario requires 1) ALTER TABLE ADD COLUMN, 2) INSERT; with this approach you only modify SELECT to analyze one more case. basically the complexity is identical to the INSERT)
• performance can be much worse if users are looking at the reports many times from the database directly, but as stated before it also guarantees that data is fresh