How to re-design my database after an assumption about its structure has changed?

Question

I am designing a MySQL database for working with elevators. The database contains an order which itself contains the technical information about an elevator (screws, ropes, material, ...) and also information about the billing-adress, the location-adress etc.

I pull every information from my database and create an orderObject with it, which will get processed further. So the orderObject needs the information from all tables and all columns.

Therefore I've created several tables from which I pull my information and update the tables with information from the company database.

Since an elevator has a surprising amount of components, I need many tables and therefore joins, which already feels like non-optimal design:

SELECT * FROM orders
JOIN locationAdress ON orders.locationAdressid = locationAdress.locationAdressid
JOIN operatorAdress ON orders.operatorAdressid = operatorAdress.operatorAdressid
JOIN customerAdress ON orders.customerAdressid = customerAdress.customerAdressid
JOIN assemblyCompanyAdress ON orders.assemblyCompanyAdressid = assemblyCompanyAdress.assemblyCompanyAdressid
JOIN securityComponents ON orders.securityComponentsid = securityComponents.securityComponentsid
JOIN normalComponents ON orders.normalComponentsid = normalComponents.normalComponentsid
JOIN liftType ON orders.liftType_id = liftType.liftType_id
JOIN liftKind ON orders.liftKind_id = liftKind.liftKind_id
JOIN shaftRealization ON orders.shaftRealization_id = shaftRealization.shaftRealization_id
JOIN engineType ON orders.engineType_id = engineType.engineType_id
JOIN engineLocation ON orders.engineLocation_id = engineLocation.engineLocation_id
JOIN enEvKit ON orders.enEvKit_id = enEvKit.enEvKit_id
JOIN approval ON orders.approval_id = approval.approval_id
JOIN precheckcompany ON orders.preCheckCompany_id = precheckcompany.preCheckCompany_id;

While creating the database I did not have all the information I needed from the company database in order to perfectly structure it, so I "guessed" how it might work.

For example: I thought that the securityComponents for an elevator are "one set of attributes", which is fixed and only differs from elevator type to elevator type. So each order in the orders-table has the ID of the row in the securityComponents-table that matches it.

Now I found out that this assumption is not true, each elevator can have a combination of individual securityComponents!

The table securityComponents looks like this:

securityComponents_catchingDeviceCarType
securityComponents_catchingDeviceCarManufacturer
securityComponents_catchingDeviceCarCertificate
securityComponents_catchingDeviceCounterWeightType
securityComponents_catchingDeviceCounterWeightManufacturer
securityComponents_catchingDeviceCounterWeightCertificate
securityComponents_doorClosureShaftType
securityComponents_doorClosureShaftManufacturer
securityComponents_doorClosureShaftCertificate
securityComponents_speedDelimiterCarType
securityComponents_speedDelimiterCarManufacturer
securityComponents_speedDelimiterCarCertificate
securityComponents_speedDelimiterCounterWeightType
securityComponents_speedDelimiterCounterWeightManufacturer
securityComponents_speedDelimiterCounterWeightCertificate
securityComponents_mountBufferCarNumber
securityComponents_mountBufferCarType
securityComponents_mountBufferCarManufacturer
securityComponents_mountBufferCarCertificate
(...)

So I thought of splitting up the securityComponents table in smaller tables for the mountBuffer, speedDelimiter, ... but this would blow up my already big SELECT * FROM orders ... JOIN even further since I would need to join those tables too.

I've read that 15 joins might be okay, but more (and I would have around 30+) might be a performance problem.

How can I fix this situation? Should I re-design my database and if yes, how?

---

Edit: Rick James posted a possible solution like this:

+---------------+----------------+------------------------+-----------------------+----------------+-----------+
| components_id | component_name | component_manufacturer | component_certificate | component_type | orders_id |
+--------------------------------------------------------------------------------------------------------------+
|             1 | catchingDevice | companyA               | certificate234        | security       | 1         |
|             2 | mountBuffer    | companyA               | certificate2234       | normal         | 17        |
|             3 | platine1       | companyC               | certificate6734       | normal         | 55        |
|             4 | platine2       | companyF               | certificate3454       | security       | 55        |
|             5 | platine3       | companyC               | certificate2388       | security       | 23        |
+---------------+----------------+------------------------+-----------------------+----------------+-----------+

Basically I would merge the 2 tables securityComponents and normalComponents into a single table named components and create an extra-column to determine what kind of component (security / normal) it is.

I am not creating an invoice, but I create the big orderObject to push it around in my program, set / change it's attributes and in the end use it to generate different PDF documents with it, so the intention is pretty similar.

I would then remove the securityComponents-colum from the orders-table and link from the components-table to the orders-table via the orders_id.

Then I would need 2 Select-statements (for the order-table and the components-table) in order to get all attributes and then join them with a single JOIN. This does not change the complexity of the big SELECT-statement with the many joins, but would make the components-table pretty clean and easy to understand. Also, if later the need arises to add new components, this would be really easy.

I dont seem to understand how the connection to the orders-table would work: For a single order, would I have to create 50 entries in the components-table, each with an order_id? So for 100 elevators I would have to create 5.000 lines in the components-table?

Would this solution work the way I intend it to, or are there problems I am not yet aware of?

Answer 1

There is a hard limit of 61 JOINs. The efficiency of multiple joins depends on a lot of things. A query with 30 joins may run fine, but be a pain to read.

The query you listed does not seem to be anything more than normalization? An alternate syntax (of dubious merit) is

SELECT ...,
    ( SELECT lift_name FROM liftType WHERE orders.liftType_id = liftType_id
        ) AS LiftName,
    ...

I see another pattern... "Components" seem to have Type, Manufacturer, and Certificate. Those 3 columns (plus an id) could be in a table for all Components. This would cut down on the number of tables, but not necessarily the number of JOINs.

What are you doing with the big query? Building an Invoice? Maybe it would make sense to have several SELECTs, broken down by areas of the invoice.

Answer 2

A few suggestions before I even start:

• Use aliases. Please!
• Don't name columns using the template "tablename_columnname". Just use the column name.
• The surrogate key field of all tables can be simplified, like "ID"

Here is your query using these suggestions:

SELECT * FROM orders o
JOIN locationAdress la ON o.locationAdressid = la.ID
JOIN operatorAdress oa ON o.operatorAdressid = oa.ID
JOIN customerAdress ca ON o.customerAdressid = ca.ID
JOIN assemblyCompanyAdress aca ON o.assemblyCompanyAdressid = aca.ID
JOIN securityComponents sc ON o.securityComponentsid = sc.ID
JOIN normalComponents nc ON o.normalComponentsid = nc.ID
JOIN liftType lt ON o.liftType_id = lt.ID
JOIN liftKind lk ON o.liftKind_id = lk.ID
JOIN shaftRealization sr ON o.shaftRealization_id = sr.ID
JOIN engineType et ON o.engineType_id = et.ID
JOIN engineLocation el ON o.engineLocation_id = el.ID
JOIN enEvKit k ON o.enEvKit_id = k.ID
JOIN approval a ON o.approval_id = a.ID
JOIN precheckcompany pre ON o.preCheckCompany_id = pre.ID;

Not nearly so scary.

However, the bad news is there is probably little or nothing you can do to simplify this query. The worse news is that you describe a m-m relationship between the orders and securityComponents tables. So the one line:

JOIN securityComponents sc ON o.securityComponentsid = sc.ID

will be replaced by something like this:

JOIN OrderSecComp osc ON osc.OrderID = o.ID
JOIN securityComponents sc ON osc.securityComponentsid = sc.ID

If you split up the securityComponents table as you mentioned, there would have to be two additional joins for each new table! Not the direction you wanted to go but there doesn't seem to be any reason to split it up anyway.

There is not enough information to tell if the schema can be redesigned, but the chances are slim that it would be possible. Besides, redesigning a schema just to improve one query is almost inevitably a Bad Idea.

This query is not complicated. It is tedious. Sometimes complicated queries can be simplified. Tedious queries...not so much. Sorry.