CODEWITHSUNDEEP
ruby-on-railsactiverecorddatabase-designrelational-database
Bharat
Bharat

Reputation: 3010

Creating a database model with categories and multiple features

I am building a rails site which rates places.

Each place belongs to a category. Each category has many features. Thus a place can belong to only one category but can have many of it's features selected. If a place belongs to category 1, then it can have only the (1 or many)features of category 1 and not anything else. Also each place age groups.

What would be the rails way to accomplish this?

I am using SQLite for dev & would be using postgres(on Heroku) for production.

This is the table structure I have thought of(table & it's columns):

Place table:

INTEGER id 
STRING name
INTEGER category_id
STRING address
FLOAT rating

Category Table:

INTEGER id
STRING name

Features Table:(The form would show features per category to be selected by querying this table)

INTEGER id
STRING name
INTEGER category_id

AgeGroup Table:(A Place can have multiple age groups selected)

INTEGER id
INTEGER place_id
BOOLEAN zero_to_three
BOOLEAN four_to_eight
BOOLEAN nine_to_twelve
BOOLEAN thirteen_above

PlacesFeatures Table:

INTEGER id
INTEGER place_id
INTEGER category_id
BOOLEAN feature_1
BOOLEAN feature_2
...
BOOLEAN feature_n

Relationships:

Place 
   belongs_to :category
   has_many   :age_groups

Category 
   has_many :places
   has_many :features

AgeGroup
   belongs_to :place

A Place entry would be creating through a form submission.

Thank you.

Upvotes: 0

Views: 107

Answers (1)

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10703

You should either have boolean flags for age groups in Places table or separate PlacesAgeGroups table like this:

PlacesAgeGroups Table:

INTEGER id
INTEGER place_id
INTEGER age_group_id

AgeGroups Table:

INTEGER id
INTEGER description

Similarly, PlacesFeatues should rather have references to featues, one feature per record.

PlacesFeatures Table:

INTEGER id
INTEGER place_id
INTEGER category_id # (this is optional, as it's already set in Places table)
INTEGER feature_id

Upvotes: 1

Related Questions