Cookies vs Session vs Database

Question

I have a website that customers can log into and buy products from a specific range (based on their customer number). The navbar has a dropdown for product categories, but doesn't show any where there would be no products.

At the minute, the database is queried on each page to populate the product categories menu, but this feels inefficient. I'm now wondering if the list of 'allowed' products should be stored in either cookies or session variables at the beginning of a session.

From what I have gathered from other questions, a session variable would normally be used, but they have warned against storing a lot of data in session variables. In this case, I would be storing a potentially large list of products for each user, so would cookies be better? I wouldn't class a list of products as being particularly sensitive, and I would do a server-side check before placing the order. Or should I stick with the current solution of querying the database each time?

To be clear, I will still store the information in the database as well, this question is only asking about a temporary storage for quick access throughout the session.

I have already looked at the following questions, but I still don't feel like I've quite got the answer for my particular scenario, and the questions that are close haven't mentioned cookies.

• Cache VS Session VS cookies?
• Cookie VS Session
• Session Cookie vs Persistent Cookie
• Trade off between user data in session vs database?
• session variables vs database

Answer 1

The approach I would take would probably be something like this:

• Store the product data in a cache that is shared between all customers and use the primary key of each product as (part of) the cache key for each product. The cache strategy used could vary depending on scalability requirements (System.Runtime.Caching vs redis vs file caching). In general, I would probably use a sliding expiration or LRU cache (see this .NET example) for the product data so the most popular products stay cached longer than less popular products. When a request comes for a particular primary key, the cache is checked first for the key and if it returns null, the product is looked up from the database and cache populated before returning the product (see this example).
• For each customer, store only the primary key and other relevant data (what class of discount they may get, etc.). If the primary key list is small enough, you can probably get by with a cookie (encrypted) for this. If you need this to scale to give each customer more than will fit in a cookie, then query the database for the primary keys.
• When the customer requests the data, get the list of primary keys first, then use those keys to access the pre-cached product data to build the view. You can store as many different primary key lists as you need to meet your requirements without having to get the product data from the database for every customer or every use case for an individual customer.

Think twice about using session state - the advice there is not to use session state for user profile data.

Of course, the above doesn't take into consideration any other marketing requirements that may be needed. So you may need to adjust this strategy for your needs. For example, if you need to update the product information faster than the cache would normally expire, you might consider an approach that updates the product data in the database first and, if successful, gets a write lock on the cache and updates the cache, too. That would allow for near-real-time cache updates without having to invalidate and reload the cache from the database.