Alter table vs new table with foreign keys

Question

I have a existing database that needs some changes done. I need to decide whether to alter a table to record the extra data or use the existing table that already records that data for a separate test and link it to the other table with new table.

Existing:

tSubTest(ixSubTest (pk), ixTest (fk))
tPressureVolume( ixPressureVolume (pk), ixSubTest (fk), ...data fields 1...)
tMotorData( ixMotorData (pk), ixSubTest (fk), ...data fields2...)

Option 1:

tSubTest(ixSubTest (pk), ixTest (fk))
tPressureVolume( ixPressureVolume (pk), ixSubTest (fk), ...data fields 1...)
tMotorData( ixMotorData (pk), ixSubTest (fk), ...data fields2..., ...data fields 1...)

Option 2:

tSubTest(ixSubTest (pk), ixTest (fk))
tPressureVolume( ixPressureVolume (pk), ixSubTest (fk), ...data fields 1...)
tMotorData( ixMotorData (pk), ixSubTest (fk), ...data fields2...)
tMDPVLink( ixMDPVLink (pk), ixMotorData (fk), ixPressureVolume (fk))

Basically the way it was working was there was a test to check pressure volume once at the start of the test sequence. Now they want to record it every five minutes with the other electrical data. They will still be performing the initial pressure volume test.

Answer 1

I have a existing database that needs some changes done. I need to decide whether to alter a table to record the extra data or use the existing table that already records that data for a separate test and link it to the other table with new table.

I wouldn't put something into a separate table with a reference just based on the fact it's being added late in the project.

Try to keep those bits of information together that belong together --> I would rather update existing tables with an extra column or two, than create an artificial, new "linked" table.

The only exception to this rule might be if you have a substantial number of columns (say: 10 or more) that form a logical entity and that are only ever going to be present in your business case in less than 10% of the cases.

E.g. if you have a specific type of customer which needs lots of extra fields, but that's really only a handful of customer (like your "Gold" customers), then it might be better to put those "clusters" of data into a separate table and link it up - since otherwise a whole bunch of fields will all be empty (NULL) for the vast majority of your database entities.

Hope that helps a bit - just my own 2 cents ;-)

Answer 2

If all data (motor + pump) is sampled at the same time, put everything in one table, like this:

If pressure/volume is sampled independently from motor data, use this: