I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods.
Option 1: Serialize the array as JSON/PHP serialized array and store it in a 'meta' column.
id | name | meta
1 Bob {"city":"GoTown","birthdate":"1980\/8\/14","cat":"Felix"}
2 Alice {"city":"Streamville","birthdate":"1986\/6\/6","dog":"Rex"}
Option 2: Store keys and values together in a stack.
user_id | key | value
1 name Bob
1 city GoTown
1 birthdate 1980/8/14
1 cat Felix
2 name Alice
2 city Streamville
2 birthdate 1986/6/6
2 dog Rex
Option 3: 3 tables: entites, keys, values. Store each key name only once.
user_id | name
1 Bob
2 Alice
key_id | keyname
1 city
2 birthdate
3 cat
4 dog
user_id | key_id | value
1 1 GoTown
1 2 1980/8/14
1 3 Felix
2 1 Streamville
2 2 1986/6/6
2 4 Rex
Are there any pitfalls with using any of these strategies? I'd like to eliminate some of them if they have insurmountable disadvantages.
EDIT: Added some data to represent schema-less data