Lets say, I have at the moment, the following tables:
categories
- id
- name (for example "museum", "tower" or "theater")
- some other fields...
countries
- id
- name
- some other fields...
cities
- id
- name
- country_id
- some other fields...
sights
- id
- name
- city_id
- category_id
- some other fields...
My question / problem
Now I want to combine all these table into a single table which I call "objects". Since the different tables avove haven't the same "some other fields.."-Fields, I need a flexible way of storing this data. There are 2 options:
- Storing it as a json-string in a field called "data" in the object-table
- Or storing it in a separete table "properties" (object_id, field_name, field_value)
My Questions:
- But which version would be better/has a better performace? "Json to Object conversion in PHP and string-search" vs "multiple joins"?
- For the second version: Which datatype should the field "field_value" have? This value can be filled with anything. From a boolean value to strings.
