Check your solution/ technical architecture knowledge – Design DB for User Interests List on ecommerce application

Matching Technical knowledge with various business scenarios often helps us to multiply our knowledge. In this blog I have listed one of the  scenario which I came across on my experience. Read the scenario and apply your thought process to refine your knowledge.Kindly note that this is my draft version and still enhancing improve further, your suggestions and comments are most welcome.

Scenario:  Business would like to develop a e-commerce site like amazon.com, and one of the requirement is users should get list of their interested products when they first login t a system. Design DB model for this requirement, and the DB relations should explain about all the relationship across a tables.

Expected Though Process:

  1. Find out basic buliding blocks, various systems need to handshake
  2. Build solution architecture
  3. OLTP vs OLAP (RDBMS vs NOSQL)
  4. detailed technical / application architecture
  5. pros, cons of each of the approach
  6. NFR details

Answers:

User Interest data can be collected from
a. user browser behaviour
b. order history
c. user profile

RDBMS approach
Following Tables are involved
a.Users
b.UserInterest
c.product
Relationships
a.One to many between user and user interest
b.many to many between userinterst and product tables.

NOSQL Approach:

As a good alternate option, we can Leverage NOSQL databases

References

  1. http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships–net-8561
  2. http://codeverge.com/asp.net.sql-datasource/database-design-help/530713
  3. http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
    sql server guide
  4. http://www.techonthenet.com/sql_server/foreign_keys/foreign_keys.php
  5. https://howtoprogramwithjava.com/database-relationships-many-many-one-one/
  6. http://www.lornajane.net/posts/2011/inner-vs-outer-joins-on-a-many-to-many-relationship

Notes:

Cross Join(outer join)- no condition, cartesian product result
natrual join – only matching common field
inner join – This query will return all of the records in the left table (table A) that have a matching record in the right table (table B).
SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

left join – This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B)
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

Right join- This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A)
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Outer Join –
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B)
SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

Left Excluding Join –
This query will return all of the records in the left table (table A) that do not match any records in the right table (table B)SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Right Excluding JOIN
This query will return all of the records in the right table (table B) that do not match any records in the left table (table A)
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

Outer Excluding JOIN

his query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match.
SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s