I am wondering which is the best approach to handle relationships in my data.
My person table currently stores 'name' and 'url' for multiple instances of the following (column names changed for simplicity):
- Favourite books
- Favourite websites
- Favourite shops
- Favourite places
So, for each of the above, I have a column in the person table containing a JSON string. For example, for a single person, I might have the following in the 'favourite_shops' column:
[{"name":"john lewis", "url":"http://www.johnlewis.com"},{"name":"tesco", "url":"http://www.tesco.co.uk"}]
And the following in the 'favourite_websites' column
[{"name":"bbc", "url":"http://www.bbc.co.uk"},{"name":"the guardian", "url":"http://www.guardian.co.uk"}]
Obviously these JSON strings can contain many more items, and this is repeated for the other two columns - 'favourite_books' and 'favourite_places'.
So, one query to the database can return all the data I need. But I must then loop through the JSON in AngularJS for display on the front end.
My question is this - would it be beneficial (especially due to the front end processing required), to have 4 join tables instead of the columns listed above?
Or perhaps a single jointable (as the 4 share the same properties, name and url) with an additional column indicating which of the 4 the row refers to?
Things to add:
- I am using Laravel 5.2 and Eloquent
- It is very unlikely I will ever need to display all 'persons' AND their related data at once, the view will be one person at a time.
- It is also very unlikely I would ever need to query specific items - ie 'favourite_shops' named 'john_lewis' - the data is supplementary to a person.
- The relationship between person and the 4 options is one to many - persons will not need to share the data, any matching entries are simply additional rows. The actual columns I am using are highly unlikely to have need to share data between persons.
- I am building the above as a RESTful api, so all data will be json_encoded.
