What are the [dis]advantages of using a key/value table over nullable columns or separate tables?

Question

I'm upgrading a payment management system I created a while ago. It currently has one table for each payment type it can accept. It is limited to only being able to pay for one thing, which this upgrade is to alleviate. I've been asking for suggestions as to how I should design it, and I have these basic ideas to work from:

1. Have one table for each payment type, with a few common columns on each. (current design)
2. Coordinate all payments with a central table that takes on the common columns (unifying payment IDs regardless of type), and identifies another table and row ID that has columns specialized to that payment type.
3. Have one table for all payment types, and null the columns which are not used for any given type.
4. Use the central table idea, but store specialized columns in a key/value table.

My goals for this are: not ridiculously slow, self-documenting as much as possible, and maximizing flexibility while maintaining the other goals.

I don't like 1 very much because of the duplicate columns in each table. It reflects the payment type classes inheriting a base class that provides functionality for all payment types... ORM in reverse?

I'm leaning toward 2 the most, because it's just as "type safe" and self-documenting as the current design. But, as with 1, to add a new payment type, I need to add a new table.

I don't like 3 because of its "wasted space", and it's not immediately clear which columns are used for which payment types. Documentation can alleviate the pain of this somewhat, but my company's internal tools do not have an effective method for storing/finding technical documentation.

The argument I was given for 4 was that it would alleviate needing to change the database when adding a new payment method, but it suffers even worse than 3 does from the lack of explicitness. Currently, changing the database isn't a problem, but it could become a logistical nightmare if we decide to start letting customers keep their own database down the road.

So, of course I have my biases. Does anyone have any better ideas? Which design do you think fits best? What criteria should I base my decision on?

Answer 1

My #1 principle is not to redesign something for no reason. So I would go with option 1 because that's your current design and it has a proven track record of working.

Spend the redesign time on new features instead.

Answer 2

Perhaps you should look this question

The accepted answer from Bill Karwin goes into specific arguments against the key/value table usually know as Entity Attribute Value (EVA)

.. Although many people seem to favor EAV, I don't. It seems like the most flexible solution, and therefore the best. However, keep in mind the adage TANSTAAFL. Here are some of the disadvantages of EAV:

• No way to make a column mandatory (equivalent of NOT NULL).
• No way to use SQL data types to validate entries.
• No way to ensure that attribute names are spelled consistently.
• No way to put a foreign key on the values of any given attribute, e.g. for a lookup table.
• Fetching results in a conventional tabular layout is complex and expensive, because to get attributes from multiple rows you need to do JOIN for each attribute.

The degree of flexibility EAV gives you requires sacrifices in other areas, probably making your code as complex (or worse) than it would have been to solve the original problem in a more conventional way.

And in most cases, it's an unnecessary to have that degree of flexibility. In the OP's question about product types, it's much simpler to create a table per product type for product-specific attributes, so you have some consistent structure enforced at least for entries of the same product type.

I'd use EAV only if every row must be permitted to potentially have a distinct set of attributes. When you have a finite set of product types, EAV is overkill. Class Table Inheritance would be my first choice.

Answer 3

At first sight, I would go for option 2 (or 3): when possible, generalize. Option 4 is not very Relational I think, and will make your queries complex. When confronted to those question, I generally confront those options with "use cases":
-how is design 2/3 behaving when do this or this operation ?

Answer 4

If I were designing from scratch I would go with number two. It gives you the flexibility you need. However with number 1 already in place and working and this being soemting rather central to your whole app, i would probably be wary of making a major design change without a good idea of exactly what queries, stored procs, views, UDFs, reports, imports etc you would have to change. If it was something I could do with a relatively low risk (and agood testing alrady in place.) I might go for the change to solution 2 otherwise you might beintroducing new worse bugs.

Under no circumstances would I use an EAV table for something like this. They are horrible for querying and performance and the flexibility is way overrated (ask users if they prefer to be able to add new types 3-4 times a year without a program change at the cost of everyday performance).