single fixed table with multiple columns vs flexible abstract tables

I was wondering if you have a website with a dozen different types of listings (Shops, Restaurants, Clubs, Hotels, Events) that require different fields, is there a benefit of creating a table with columns defined like so
Example Shop:

shop_id | name | X | Y | city | district | area | metro | station | address | phone | email | website | opening_hours

Or a more abstract approach similar to this:

object_id | name        
---------------
1         | Messy Joe's  
2         | Bate's Motel 

type_id | name
---------------
1       | hotel
2       | restaurant


object_id | type_id
---------------
1         | 2
2         | 1

field_id | name           | field_type
---------------
1        | address        | text
2        | opening_hours  | date 
3        | speciality     | text

type_id | field_id
---------------
1       | 1
1       | 2
2       | 1
2       | 3

object_id | field_id | value
1         | 1        | 1st street....
1         | 3        | English Cuisine

Of course it can be more abstract if value's are predefined (Example: specialties could have their own list)

If I take the abstract approach it can be very flexible, but queries will be more complex with a lot of joins. But I don't know if this affects the performance, executing these 'more complex' queries.

I would be interested to know what are the up and downsides of both methods. I can just imagine for myself, but I don't have the experience to confirm this.

27
задан Moak 23 January 2015 в 19:09
поделиться