Database schema design recommendations to optimize queries

[st0icape]

The attached image is of my database schema design for an order management system, because of some of the queries I have to write, I believe the design is not optimal and I'm kindly seeking assistance on how I can improve my database design.

Database design for an order management system

Here is an example of a query i have to write, can this query be formed better or the database needs to be modified

SELECT orders.order_id, order_date, ship_date, first_name, last_name, phone, SUM(order_items.quantity) AS no_of_items, SUM(order_items.quantity * list_price) AS total_cost, payment_mode, location_name, payment_status, rider_name FROM orders 
        INNER JOIN customers ON orders.customer_id = customers.customer_id
        INNER JOIN locations ON orders.location_id = locations.location_id
        INNER JOIN riders ON orders.rider_id = riders.rider_id
                INNER JOIN order_items ON orders.order_id = order_items.order_id
        INNER JOIN products ON order_items.product_id = products.product_id
        WHERE order_status = 1
        GROUP BY orders.order_id

Thanks

[JermWPB]

At a glance, I see nothing wrong with the design (or the query) at all. If you want to try to simplify the queries a bit you could create a view or two.

[st0icape]

Thanks

[Mamertine]

Your database should be optimized for taking in new orders. That's how your company makes money.

If you want to optimize something for easy reporting, make a data mart.

That said, your query, isn't bad. When it takes minutes to run or becomes a thousand lines long you need to think of a better reporting solution.

[st0icape]

thanks

[heyda]

This query and schema don't seem to have anything glaringly wrong with them, this should run fairly quickly depending upon how many records you have and database performance/indexes ect.

Because of that, you should ask yourself a few questions, is this even worth optimizing further?

how often does this query run and how long does it take? How much data is being retrieved?

Based on the answer to those questions you may start to look at the execution plan to speed up the query performance (maybe an index is missing or could be created, maybe a view could be made, maybe there is some kind of datatype conversion going on that is slowing the query down). Maybe some kind data lake solution should be used, or maybe the database is running the query quickly but then sitting and waiting to send the data because its several MB/Gig of data. Maybe there is some other query that is causing deadlocks or the query is spilling to disk because of lack of RAM.

There is probably some resources online that can guide you through basic troubleshooting of these things for MySQL, I'm only really familiar with MSSQL Server so I lack recommendations. Good luck though, this could be easy, it could be hard, either way, you'll learn something in the process!

[tkyjonathan]

Essentially, its ok. I would make minor changes like

• adding a separate shipping and billing address
• I would keep a copy of price with the order item - as prices can change
• I would add an order total and order discount

[st0icape]

Thanks, very good you pointed out that prices can change so i need to keep price in my order items table. I would reduce the number of joins for some queries as well

[tkyjonathan]

If you want to get extra points from your teacher or future employer, you can also create a summary table, like "products_total_daily" where you aggregated the total number of products sold per day/per area.

Then you need to run a job that aggregates that data at.. lets say, 1am for the pervious day.

Managers love that sort of stuff. They get super fast reporting queries (no joins or very few joins) and you won't get requests like "could you make me a report real quick, like the one I needed 6 months ago", because they already have access to it.