0

I'm trying to create a website similar to Yelp (smaller scale, smaller market) and right now i'm in the database design stage and was wondering how to model the tables for storing the 'additional business info' features that are occasionally found under some business listings such as:

  • nearest transit (text)
  • accepts credit card (Y/N)
  • has wheelchair access (Y/N)
  • good for kids (Y/N)
  • parking (enum type - street, garage, etc)
  • attire (enum type - casual, formal, etc)
  • delivers (Y/N)
  • etc

bearing in mind that some of this info only pertains to businesses of a certain category e.g. the attire and delivers info might only apply to say, businesses in the restaurants category so it wouldn't make sense to store everything in the main businesses table.

What I'm wondering is how to store these extra features/additional information about a business since they are not applicable to all businesses and not in all cases.

I was thinking of putting each feature in it's own table and linking to the main businesses table through the *biz_id* FK e.g.

Businesses
-------------
biz_id (PK)
name
website
isnew
...

Biz_accepts_credit_card
-----------------------
biz_id (FK)
accepts_credit_card (bit field)

Biz_parking (biz can have multiple parking types)
-----------
auto_id (PK)
biz_id (FK)
parking_type {any combination of: street,garage,valet,etc}
...

My thinking was that while this would create a large amount of tables just for storing this additional info, it would also be pretty flexible, especially in terms of adding new info/features further down the road, but it would probably mean that I would have to be joining lots of tables (15+ in worst case) just for this info :/

I was also wondering if the various categories a business could fall into would have any bearing on this as well.

EDIT: After reading the response from @Daveo

Features{id, name, ismultiVal} (defines all possible features)

FeatureValues{id, feature_id, value} (defines the possible values each feature can have)

BusinessFeatures{id, biz_id, feature_id, feature_value} (stores the features applicable to each business)

CategoryFeatures_{category_id, feature_id} (what features are found in which categories)

FeatureReviews_{review_id, feature_id, feature_value} (stores the feature values that users voted on in their review of a business)
Ray
  • 617
  • 8
  • 15

2 Answers2

2

I would NOT make a seperate table for each Feature. I would make a generic database structure. 

 Businesses
    -------------
    biz_id (PK)
    name
    website
    isnew
    ...

    Biz_Feature
    -----------------------
    biz_id (FK)
    feature(FK)

    Feature
    -----------
    Feature_id (PK)
    FeatureType_id (FK)
    name

    Feature_Type
    -----------
    Feature_Type (PK)
    Name

E.g.

Feature_Type
1   good for kids
2   parking

Feature
id   type_id    name
1      1         yes
2      1          no
3      2         street
4      2         garage

This is where i would start. But then add extra info to determin if the Feature should display as a checkbox or not.

Daveo
  • 19,018
  • 10
  • 48
  • 71
  • Thanks for the help, after reading your reply I think I've come up with a decent solution now. I'll update my question with the structure. – Ray May 27 '11 at 06:32
1

You might end up needing to search businesses by feature, meaning these features will need to be indexed.

What Daveo is suggesting amounts to adding an EAV store -- which is exactly what you do NOT want imo. You cannot index an EAV store efficiently.

I was thinking of putting each feature in it's own table and linking to the main businesses table through the *biz_id*

That is indeed the correct approach if you eventually need to mine the data.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
  • Denis - thanks for the pointer. Ray a good comparison of both options is discussed here http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question – Daveo Jun 25 '11 at 05:27
  • Bit late here, but thanks for the post, you guys were really hepful – Ray Aug 29 '11 at 00:20