I need to fit in additional data into a database, and I have a choice between modifying an existing table (table_existing) or creating new tables.
This is how table_existing looks like right now:
table_existing
-------------------------
| ID | SP | SV | Field1 |
| .. | WW | 1 | ...... |
| .. | WW | 1 | ...... |
-------------------------
Option (A)
table_existing
----------------------------------------------------------------------
| ID | SP | SV | Field1 | Field2 | Field3 | Field4 | Field5 | Field6 |
| .. | XX | 1 | ...... | ...... | ...... | ...... | ...... | ...... |
| .. | YY | 2 | ...... | ...... | ...... | ...... | ...... | ...... |
----------------------------------------------------------------------
Option (B)
table_existing would be converted into table_WW_1_data
---------------
| ID | Field1 |
| .. | ...... |
| .. | ...... |
---------------
table_XX_1_data
------------------------
| ID | Field1 | Field2 |
| .. | ...... | ...... |
| .. | ...... | ...... |
------------------------
table_YY_2_data
---------------------------------
| ID | Field1 | Field2 | Field3 |
| .. | ...... | ...... | ...... |
| .. | ...... | ...... | ...... |
---------------------------------
Context: The combination of SP, SV determine the "number" of fields that will be populated. For instance, (XX, 1) has 2 fields. (YY, 2) has 3 fields.
If I were to go with Option (A) I would have many empty/NULL values in the "wider" table.
If I go with Option (B), I am basically creating more tables... one for "each" combination of SP, SV - there will be perhaps 4-5 in total. But each would be fully populated with the right number of fields. table_existing would be changed as well.
What is the more optimal database structure from the speed point of view? I think that from the maintainability point of view, Option (B) might be better.
Edit1
Neither of the two Options will be the most critical / frequently used tables in my application.
In Option (B), after the data has been split up, there would be no need of JOINing them at all. If I know I need Fields for XX_1, I will go to that table.
I'm trying to understand if there are pros and cons for having ONE large table with many unused values vs having the same data split across more number of tables. Do the larger number of tables lead to a performance hit in the database (we've got ~80 tables already)?