Keys, Lists, Values

Question

I think I've worked myself into a pretty stupid corner, here. Let me show you the schema. I've got a lot of things around it already but I've suddenly realized a big problem.

Units
- UnitId

Rings
- RingId

Keys
- RingId (FK)
- KeyId (PK)
- KeyLiteral (FK)
- KeyValue

Literals
- LiteralId
- LiteralValue

It was good for a while. Basically, Units are created and then given a KeyRing. To that ring, keys are added and each key can have a value and an ID of a literal (to prevent duplication).

This was wonderful, great. Worked perfectly. Could add new properties to items without a hitch, no more hardcoded tables for it. Huzzah.

Then I realized some items needed to be a value from a list of possible items. Okay, no problem.

Lists
- ListId

ListItems
- ListId (FK)
- ListItemId (PK)
- ListItemValue

Oh yeah, now I have a way to make lists. But ....... That doesn't really help, if it's just a string value in my KeyValue that is returned from a List. If the ListItemValue changes, we don't really solve anything. So I could use the ListItemId in the KeyValue.

But then I have to figure out how to get the ListItemValue out of the ListItems table when calling a Key by nothing but it's Literal name. Also, not all Keys will be related to a list.

Any ideas?

Answer 1

Is this another re-invention of the uber-generic name-value-pair? I don't really understand the ring/units/keys thing but it reminds me of attempts I've seen in the past to make ultra-flexible name-value pairs that end up being unqueryable and perform poorly.

Identify your entities & build tables to support them.

Answer 2

"Identify your entities & build tables to support them."

"That isn't possible for this project."

If that is really so, then don't try to use relational database technology. The relational model was conceived as a general-purpose data management model in which data/information requirements were assumed to be very much stable.

If your user changes requirements faster than he changes his undies, then relational technology is not a suitable option for trying to meet his requirements.