Given the following relational model:
I need to implement a typed search for hotels, which should be able to query on name, city, country, category, hoteltype, roomprice, customtype, room custom type and roomtype or any combination of these criteria.
At the moment I'm doing this though Entity Framework, dynamically constructing a query over the Hotel-entity (IQueryable). This is fine as currently there is very little data. However, this isn't very scalable and when there is a lot of data this will become very slow as it's a query over 10 tables. Note that I only need to load the hotel-data, not the entire graph.
I'm thinking of ways to improve the scalability of this part. I have been looking into CQRS and maybe NoSQL solutions.
One approach I had in mind is having this model on the write side (to enforce consistency) and having a different model on the read side. On write, I would then update the read-model (viewmodel).
However, since this is already a more or less complex model in terms of relations, I see a few problems with that:
- Updating metadata (such as category, roomtype, hoteltype) would require me to update all of the hotels or rooms in the read-model. This could be quite slow or impossible once there's a lot of data.
- Suppose I use a document database such as MongoDB and save a hotel with all the related items denormalized, won't the query over that table still be slow as it has to search inside each document, or is still still quite fast in NoSQL?
To summarize a few questions:
- Is denormalizing this model the right approach and what would be the best way?
- Will NoSQL be faster?
- Are there any better approaches I can follow or ways to break up the relationship and flatten the hierarchy while still being able to do a typed search?
- Ideally some fields should also support fuzzy search, what would be the best way to achieve that?