Data Modeling Approaches for Big Data and Analytics Solutions - Big Data Imperatives - page 175

Below are some options for modeling the data in CFDB, in order of the lowest to the
highest de-normalization. The best option depends on the query patterns, as you'll soon see.

Option 1: Relational model
This model, Figure 6-14 , supports querying user data by user ID and item data by item ID.
But there is no easy way to query all the items that a particular user likes or all the users
who like a particular item.

UserID Name Email Item ID Title Desc

123 ABC [e-mail] 111 iPhone Apple iPhone

User Item Like User ID Item ID

1 123 111

Figure 6-14. Option 1: Logical data model in RDBMS)

Option 2: Normalized entities with custom indexes
This model, in Figure 6-15 , has fairly normalized entities, except that user ID and item ID
mapping is stored twice, first by item ID and second by user ID.

UserID Name Email Item ID Title Desc

123 ABC [e-mail] 111 iPhone Apple iPhone

User by Item User ID User ID Item by User Item ID Item ID

111 123 456 123 111 222

Figure 6-15. Option 2: Logical NoSQL data model

Here, we can easily query all the items that a particular user likes using “Item
byUser,” and all the users who like a particular item using “User byItem”. We refer to these
column families as custom secondary indexes, but they're just other column families.
Let's say we always want to get the item title in addition to the item ID when we
query items liked by a particular user. In the current model, we first need to query “Item
byUser” to get all the item IDs that a given user likes; and then for each item ID, we
need to query the item to get the title. Similarly, let's say we always want to get all the
usernames in addition to user IDs when we query users who like a particular item. With
the current model, we first need to query “User byItem” to get the IDs for all users who
like a given item; and then for each user ID, we need to query “User” to get the username.
It's possible that one item is liked by a couple hundred users, or an active user has liked
many items: this will cause many additional queries when we look up usernames who
like a given item and vice versa. So, it's better to optimize by de-normalizing the item title
in “ItembyUser” and username in “UserbyItem” as shown in option 3 (Figure 6-16 ).