The entity Rental has a relationship with the following tables;

• Inventory. A rental refers to a single inventory (essentially, a copy of a DVD). An inventory might be part of several rentals. This is a one-to-many relationship.

• Staff. A rental is taken care of by a single staff member, A staff member might take care of several rentals. This is a one-to-many relationship.

• Customer. A rental is made by a single customer. A customer might make several rentals. This is a one-to-many relationship.

• Payment. A payment is relative to a single rental. A rental is associated with a single payment. This is a one-to-one relationship.

For the table Rental we have:

• Column rental_id, integer, 4 bytes.

• Column rental_date, timestamp with time zone, 8 bytes.

• Column inventory_id, integer, 4 bytes.

• Column customer_id, smallint, 2 bytes.

• Column return_date, timestamp with time zone, 8 bytes.

• Column staff_id, smallint, 2 bytes.

In total, a row in table Rental needs 28 bytes of storage.

For the table Customer we have:

• Column customer_id, integer, 4 bytes.

• Column store_id, smallint, 2 bytes.

• Column first_name, text, 6 bytes (average).

• Column last_name, text, 7 bytes (average).

• Column email, text, 25 bytes (average).

• Column address_id, smallint, 2 bytes.

• Column activebool, boolean, 1 byte.

In total, a row in table Customer needs 47 bytes of storage.

The relationship between the two given entities is one-to-one. Therefore, we can use a denormalized schema in MongoDB.

That is, we can create one single collection to store the information about the two entities.

We have two options:

• We create a collection Payment, where each document contains the attributes of a payment and an embedded document with the details of the rental the payment refers to.

• We create a collection Rental, where each document contains the attributes of a rental and an embedded document with the details of the payment for the rental.

Although both options are perfectly valid, we prefer the second one, as rentals are our first-class citizens in our database.

We can also consider the attributes of a payment as attributes of a rental, without creating an embedded document for the payment.

We’ve seen before that each rental needs 28 bytes of space. To make the computation easier, we round this size up to 32 bytes (a power of two). Considering that 16 MB = \(16 \times 2^{20}\), the maximum number of rentals that we can store for a given customer is given by:

\[ \frac{16 \times 2^{20}}{32} = 2^{-1} \times 2^{20} = 2^{19} \]

This gives around 600,000 rentals.

We previously found out that for each customer we need 47 bytes and for each rental we need 28 bytes.

A document that holds the data about a customer and the list of all the rentals of the customer will need \(47 + 27 \times 28 = 830\) bytes.

Hence, the total size of the Customer collection is \(803 \times 599 = 480977\) bytes, that is 470 KB.

A document that holds the data about a rental and its customer needs $ 28 + 47 = 75 $ bytes.

Hence, the total size of the Rental collection is \(75 \times 16044 = 1203300\) bytes, that is 1,1 MB.

Advantages of solution 1.:

• There is no redundancy. In fact, a rental is relative to at most one customer, therefore the data on a rental is not duplicated across different documents.

• As a result, the size of the collection is smaller than the collection in solution 2.

• For each customer, we retrieve the information on all his/her rentals with only one read operation

Disadvantages of solution 1.:

• There is a limit (albeit an acceptable one) on the number of rentals that we can store for each customer.

• We lose a “rental-centric” view of our data. As a result, if any other document in another collection (e.g., staff) refers to a rental, all the information about a rental must be denormalized in that document.

Advantages of solution 2.

• A “rental-centric” view of our data. Aggregating information from different rentals does not require digging rentals out of several lists.

• The size of each document is small and will never exceed the 16 MB limit.

• As a result, reading a document from the collection takes less time and memory than reading a document from the collection in solution 1.

Disadvantages of solution 2.

• There is a lot of redundancy. The information about a customer are replicated each time the customer makes a rental.

• As a result, the size of the collection is much higher than in solution 1.

Solution 1

We need to somehow link staff and inventory to the relative rental. We have three options:

• We embed staff and inventory into each rental document, which, let’s recall it, is already embedded in an array. This creates redundancy, as a staff member or an inventory item can appear in more than one rental.

• We create three separate collections (Customer, Staff and Inventory) and in each we embed an array with the list of the relative rentals. The problem is that the data on the rentals are now replicated three times. This solution is particularly bad, because rentals are frequently written. When we create a rental, we need to write three documents; when a customer returns an item, we need to update the return date in three documents.

• We create four collections (Customer, Staff, Inventory and Rentals); for each customer, staff and inventory we keep a list of rentals, each item being the identifier that refers to the appropriate rental. We fall back to the normalized schema of the PostgreSQL database. Then, it isn’t clear how this normalized schema will help horizontally scale the database.

Solution 2

We have only one collection Rental; in each document, we have an attribute customer, whose value is an embedded document with all the information about a customer, an attribute staff, whose value is an embedded document with all information about a staff member, and an attribute inventory, whose value is an embedded document with all information about an inventory item.

This solution has higher storage requirements than the options presented in solution 1, but it has the clear advantage of being a denormalized schema, where we control every facet of a rental (customer, staff, inventory). Moreover, when we create a rental, we only write one document ; when we update the return date of a rental, we only update one document.

Let’s recall that the size in bytes of a document storing the information on a customer is 47 bytes.

The size of the only collection Rental (hence, of the whole database) is:

\[ N_{rentals} \times (47 + 84000 + 16) = N_{rentals} \times 84063 \]

With 10,000 rentals, the size of the database is 800 MB. With 100,000 rentals, the size of the database is 7 GB. With 1,000,000 rentals, the size of the database is 78 GB.

While modeling data for a MongoDB database, the choice between a normalized and a denormalized schema does not need to be a black and white one.

We can have a denormalized schema, where we embed in the same documents information that are queried together, while storing in a separate collection the information that are rarely queried.

For instance, the profile picture of a staff member might not be an important piece of information while we’re trying to analyze which staff members are the more productive ones.

So, we might add another collection Staff where each document only contains attributes that are not in embedded documents in the collection Rental.

Here is a solution with three different collections. The information on inventory are completely denormalized into the collection Rental. For customers and staff members we only keep the necessary information and we normalize the information that are less likely to be queried while analyzing the rentals.

Let’s try to estimate the savings in terms of storage. In a document of the collection Rental, we have the following attributes:

• rental_id: 4 bytes.

• rental_date and return_date: 16 bytes.

• inventory_id: 4 bytes

• film_id and store_id: 4 bytes

• customer_id: 4 bytes

• customer first and last names: 13 bytes.

• country: 9 bytes (on average a country name has 9 characters).

• staff_id: 4 bytes

• staff first and last name: 13 bytes.

In total a document in our collection Rental will have 71 bytes (a huge improvement wrt the 84000 bytes of our first solution).

Of course, the size of a document in the Staff collection will be around 84000 bytes (we still store the profile picture). However, the number of staff members \(N_{staff}\) is much lower than the number of rentals \(N_{rentals}\).

It is easy to verify that the size of a document of the collection Customer is 34 bytes (same information as before except first and last name). Again, the number of customers \(N_{customers}\) is much lower than \(N_{rentals}\).

We assume that each customer has on average 30 rentals and for 100 customers we have 1 staff member. We have that:

• $N_{customer} = N_{rental}/30 $

• $N_{staff} = N_{customer}/100 = N_{rental}/3000 $

So, the size of the database will be given by:

\[ 71\times N_{rentals} + 84000 \times N_{rentals}/3000 + 34 \times N_{rentals}/30 = 100 \times N_{rental} \]

If we want to compare against the first solution:

• With 10,000 rentals, the size of the database is 976 KB (instead of 800 MB). With 100,000 rentals, the size of the database is 9,5 MB (instead of 7 GB). With 1,000,000 rentals, the size of the database is 95 MB (instead of 78 GB).

As you can see, a big improvement! And we have a denormalized schema that lets us take advantage of the horizontal scalability of MongoDB.

Collection Staff

• The attribute address_id refers to a full address. We can fully denormalize this information into the documents of the collection.

• The attribute store_id refers to the store where the staff member works. The Store table in the original PostgreSQL database does not have too many columns. Therefore, it might be reasonable to fully denormalize these data. However, information on the stores are also linked to customers and to inventory items. If we need to update, say, the manager of a store, we would need to update three different documents. Hence, we prefer to create a collection Store and keep in the documents of the collection Staff only the city and country of a store (and its identifier of course).

Attribute Inventory in collection Customer

• Same considerations for the attribute store_id.

• The attribute film_id is the reference to the film the inventory item is relative to. The table Film in the original PostgreSQL database contains 14 columns. This high number of attributes advises us against a full denormalization, considering that a film can be relative to multiple inventory items. We might only keep the film title and the film categories. The rest of the attributes are kept in documents of a separate collection Film.

Collection Customer

Same considerations for the attributes store_id and address_id.