Is Entity Inheritance better done thru a single table or multiple?

Question

I have a table that holds agreement information. It works well for 95% of the agreements we record.

But there is a certain type of agreement that would require another 6 or so fields to capture info specific to that type of agreement.

My question is if its better to just add those 6 fields to the existing agreement table knowing that the info is meaningless to many of the agreement records or if its better to create another table w/ a 1:1 relationship w/ the original agreement table to extend it in the case of these special types of agreements.

Neither option is all that attractive to me, but I wanted to know if one was considered a better practice than the other when you have a choice.

Thanks for any help.

Answer 1

My choice is a separated table, because for 95% of agreements its pointless information.

Answer 2

Multiple tables are probably your best bet from an extensibility perspective. Although you currently have a single agreement type that requires extra fields, the very existence of such a thing suggests that similar variations in the future are worth accomodating in your design. When other variants arise, a multiple table approach will allow you to include those agreement types and their characteristic attributes gracefully.

Also, it would be worth considering an agreement_type attribute (or something similar) in your superclass table, for cases in which you may want to perform agreement type analysis without incurring the hit of a join to the subclass table(s). The intended usage of the data will be your guide as to whether or not such an attribute makes sense.

Answer 3

It depends.

A single table is easier to query, and 6 additional fields aren't that much in the way of additional storage. If the number of rows in your existing table is small, or the number of existing columns is already pretty big, then on balance I would add the additional fields to the existing table.

On the other hand, if this change would massively inflate the size of an already-large existing table, it would definitely be worth considering setting up a new table.

Answer 4