Requirement:

1. User can write review
2. User can read review / sort review by time/ by upvote count
3. User can upvote for review
4. User can get score with high review upvote count

Not in scope:

Location proximity

​

Similar system

1. Amazon product review
2. Tripadvisor review
3. Airbnb host review

Non functional requirements:

1. Scale
2. Low latency

​

Sql Database design

Business table :

Id, name, location

Review table:

Review_id, author_id, biz_id(FKey), review_text_id, star_rating, create_ts, vote_count

​

Business → reviews (1 -- n)

Read heavy traffic

To get query result fast, indexing on vote_count, create_ts

​

​

​

​

​

What about NoSql ?

What type of No sql db:

Document db?

Key value store?

Columnar DB?

Schema?

​

Summary from Discussion

Can use Cassandra (Columnar db)

1. Write is Fast (append only)
2. More scalable compare to Sql DB
3. Multi-level index: business_id → Reviewer id → Timestamp
4. Can use range slice query for review sorted by time

Design for Upvote count

1. Atomic counter is not needed in this scenario
2. We do need record who voted which review in DB, this is needed to prevent fraud
3. The counter can be store in redis like in memory cache, can be synced with db or load from db periodically.
4. The count display can be eventually consistent.

​

Review search system

how the review full text search works

Elastic search

Kafak

query

Index module

update

Resource

https://www.youtube.com/watch?v=qAN6iyYPbEE

Starting at 15:50, John Billings talks about use elasticsearch in

Review search

https://www.youtube.com/watch?v=1D1ED4KxxWQ&t=356s

Use elasticsearch in Yelp

​

​