Data integrity/operation performance optimization in relational databases

Question

Recently come across an unusual scenario while normalizing an old relational database.

The Facts

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.

Answer 1

Great first question and welcome.

Short answer:

Make shure every tables has a PK, that theres is a FK for every relationship and to add indexes on columns that will be queried often, and create views that abstract you from the joins if you don't want to said joins every time. Performance will be good. RDBMS are optimized to do exactly that.

Long answer including some observations bases on limited information:

1. WORK_INFORMATION and REPAIR_ACTIVITY having a 1:1 relationship, they have a high change of being the same entity. One possible exceptions is if WORK_INFORMATION has columns that do not apply to all REPAIR_ACTIVITYs, but, given the relationship is 1:1, I would considered whether or not they should be two separate tables. Also, 1:1 relationships are possible conceptually but not physically since you cannot insert in either table until a matching wor exists in the other... unless you are enforcing the integrity fully in code and not using database constraints.

2. "Adding the central table id as an index on the outer tables." First, I will assume that what you call "central table" is REPAIR_ACTIVITY. Well,those tables that have a relationship with REPAIR_ACTIVITY where REPAIR_ACTIVITY is on the one side of the relationship, should already have the PK of REPAIR_ACTIVITY as a column, i.e. a foreign key pointing to REPAIR_ACTIVITY, and all FKs are indexed. The tables that have a relationship with REPAIR_ACTIVITY where REPAIR_ACTIVITY is on the many side of the relationship shouldn't have a column with the REPAIR_ACTIVITY id. That would be physically impossible since there can be only one value on any row in the one side, making the relationship 1:1.

3. I would check the relationship between REPAIR_INVOICE and REPAIR_ACTIVITY. Is the relationship inverted (meaning REPAIR_INVOICE should be the one and REPAIR_ACTIVITY the many)? If not, disregard this suggestion and chalk it out to lack of information.

4. "...adding a type field of some kind on the central table entry would assist to identify the information that exists on the outer tables" That is in some cases unnecessary and in other cases impossible, if what worries you is complexity of queries do this: create views where you already join the tables. Then in your code you query those views. Thay way you will not have to write the joins every time, just the WHERE part to filter the selection.

5. Regarding performance, adding join tables where they are needed, shouldn't harm performance if all tables have a PK, there is a FK for every relationship and there are indexes on those columnes that will be queried often (PKs and FKs already have indexes).