Recently come across an unusual scenario while normalizing an old relational database.
There's ~8 tables linked to a central table, where references between the numerous tables may or may not exist.
There's a lot of data used, and while this table was initially designed, created and mutated over time, performance was taken into account. Thus, there were no linking tables made between the central & other tables.
I'm looking to improve the structure of this, but not sacrifice query performance, since information from all of the tables can be used for reporting.
Question: How would one go about optimizing/normalizing a table structure like the one mentioned, without sacrificing the performance factor?
I've considered the following options:
1: Adding linking tables between them would make queries quite advanced, where if a lookup is done, all of the pivot tables would need to be searched to identify any relations between them.
2: Adding the central table id as an index on the outer tables. This is an idea, but then again, to find any information, you'd need to query the other tables. To maybe improve on this, adding a type field of some kind on the central table entry would assist to identify the information that exists on the outer tables.
Edit - ERD Example
As an example, consider a computer repair store. For the purposes of the example, the following assumptions are made:
- This is a small sub-system, a customer could have come in to buy an RGB fan, and didn't bring a device in. Thus, Customers don't have to have a CustomerDevice
- When a Repair is done, and a RepairActivity is recorded, the customer could bring it back in because of whatever reason.
- For every RepairActivity, a 1 to 1 relationship can be seen between the WorkInformation table.
- A RepairInvoice is only generated when the customer comes into the store, thus, a RepairActivity can exist without one.
- A Customer may have a CustomerDevice registered on the system, and not have brought it in for a repair.
Now this is a basic example, and the system mentioned in the question has many more tables attached.