Simulating variable column names in sqlite

Question

I want to store entries (a set of key=>value pairs) in a database, but the keys vary from entry to entry.

I thought of storing with two tables, (1) of the keys for each entry and (2) of the values of specific keys for each entry, where entries share a common id field in both tables, but I am not sure how to pull entries as a key=>value pairs in sql with this sort of configuration.

Is there a better method? If this is not possible in sqlite, is it possible in mysql? Thanks!

Answer 1

It sounds like you are looking for the Entity-Attribute-Value model.

Alternatives are to create different tables for different types of entities, or to have a table with a column for every possible key and set the value to NULL for entities that don't have that key.

You might want to take a look at Bill Karwin's presentation SQL Antipatterns where he covers some of the pros and cons of the EAV model and suggests possible alternatives. The relevant part starts from slide 16.