Suppose you were setting up a database to store crash test data of various vehicles. You want to store data of crash tests for speedboats, cars, and go-karts.
You could create three separate tables: SpeedboatTests, CarTests, and GokartTests. But a lot of your columns are going to be the same in each table (for example, the employee id of the person who performed the test, the direction of the collision (front, side, rear), etc.). However, plenty of columns will be different, so you don't want to just put all of the test data in a single table because you'll have quite a few columns that will always be null for speedboats, quite a few that will always be null for cars, and quite a few that will always be null for go-karts.
Let's say you also want to store some information that isn't directly related to the tests (such as the employee id of the designer of the thing being tested). These columns don't seem right to put in a "Tests" table at all, especially because they'll be repeated for all tests on the same vehicle.
Let me illustrate one possible arrangement of tables, so you can see the questions involved.
Speedboats id | col_about_speedboats_but_not_tests1 | col_about_speedboats_but_not_tests2 Cars id | col_about_cars_but_not_tests1 | col_about_cars_but_not_tests2 Gokarts id | col_about_gokarts_but_not_tests1 | col_about_gokarts_but_not_tests2 Tests id | type | id_in_type | col_about_all_tests1 | col_about_all_tests2 (id_in_type will refer to the id column of one of the next three tables, depending on the value of type) SpeedboatTests id | speedboat_id | col_about_speedboat_tests1 | col_about_speedboat_tests2 CarTests id | car_id | col_about_car_tests1 | col_about_car_tests2 GokartTests id | gokart_id | col_about_gokart_tests1 | col_about_gokart_tests2
What is good/bad about this structure and what would be the preferred way of implementing something like this?
What if there's also some information tha