Simulating variable column names in sqlite

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, 开发者_如何学Gois it possible in mysql? Thanks!

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.

@Mark Byers is right, this is the EAV model. You should read Bad CaRMa before you go down that dark path. It's a story of how this database design practically destroyed a company.

In a relational database, every row in a relation must include the same columns. That's part of the definition for a relation. This is true in SQLite, MySQL, or any other relational database.

Also see my presentation Practical Object-Oriented Models in SQL or my book SQL Antipatterns, in which I show the problems caused by the EAV model.

If you need variable columns per entity, you need a non-relational database. There are document-oriented databases like CouchDB or MongoDB that are catching on in popularity.

Or try Berkeley DB if you want an embeddable single-user solution like SQLite.