The Name-Value-Pair Model For configuration-only data

Question

I read that the Name-Value-Pair Model in database design is an anti-pattern. Essentially you have a table with two columns. One column is called 'name' and the other column is called 'value'. Let's say you are managing AWS configuration for different regions. The database structure would be as so:

name                                value
aws.new_york.access_key             jio4j54h
aws.new_york.site.user              john
aws.new_york.site.pass              eoiri4iiuh
aws.los_angeles.access_key          tret55464
aws.los_angeles.site.user           bob
aws.los_angeles.site.pass           rtry45yrt
aws.new_york.access_key             fgfhgf4fdg
aws.new_york.site.user              edward
aws.new_york.site.pass              45gfhgfhgf

The only use is to retrieve configuration:

MyApp.config.get('aws.new_york.access_key')

The other solution is to use joins. This would remove duplication and allow for referential integrity. But it becomes cumbersome:

table_aws has_many table_states which has_many table_credentials which has columns access_key, user, pass. This indeeds remove duplication, but imagine the potential for increased number of nested joins.

Given my only use case, is the Name-Value-Pair Model model still an anti-pattern or would it be suitable?

For that use case you can use a one-row table with a column per parameter. — philipxy, Oct 26 '18 at 01:29
If you really have only 3 "attributes", then, sure, a 4 column table is excellent: "aws.new_york" in one column (or maybe 2?), then a column for each of `access_key`, `site.user`, and `site.pass`. If you expect to occasionally add more attributes, then making them columns gets messy. — Rick James, Oct 27 '18 at 03:49

score 0 · Answer 1 · answered Oct 27 '18 at 03:46

The anti-pattern you are thinking of is probably Entity-Attribute-Value. You are describing a simple "hash table". If you have only a hundred rows in this table, or even a thousand, there is no problem.

But... You are really describing EAV, except that you have made it messier.

    aws.new_york.access_key

is really aws.new_york as the "Entity" and "access_key" as the "Attribute".

Typically EAV tables should be 3 columns, with a PRIMARY KEY(entity, attribute) in that order.

If you goal is simply a repository for "configuration", then you are not likely to be doing queries that get to where EAV falls apart.

So... Let's see what you will do with the dataset before passing judgement.

The Name-Value-Pair Model For configuration-only data

1 Answers1