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?
Typed search over complex model. Possible CQRS / NoSQL solution
database design;nosql;cqrs
First off, if that's your actual schema, it appears to be over-normalized.Hotel_Category | CategoriesHotel_HotelType | HotelTypesRoom_RoomType | RoomTypesHotel_Room | Roomare all candidates for being merged together by the pairing.  So instead of 8 tables you would have 4.  The double prefix on some of your tables is a hint that the normalization has been taken too far.Practical or pragmatic normalization is always a balancing act.  In this case, I think you've gone too far down the normalization route.Next up, performance on the relational DB side: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.Apologies if this sounds pedantic, but you do have indexes in place for all of those elements, right?  If sharding is the secret sauce of web scale1, indexes are a critical first step in making sure your relational DB can scale.1 The phrase is from a viral video parodying certain reasons in selecting database.  Just google the term, but know that it's an NSFW video. After that, we need to look at write models & read models.  With as small as this schema is, I think that approach is overkill, especially if you don't de-normalize the schema you provided in the question.  Taking this route is just adding gasoline to an already hot fire - all that you'll have accomplished is bringing your application down more quickly due to the complexity.That's not to say read-only views wouldn't be worth considering after you reduce the schema.  Thinking through the ways people are likely to query for rooms, you can build views by location + name, location + price, location + type, etc... Ideally, you'll have metrics from existing use to drive which views you should build.  But it sounds like you understand the domain well enough that you can take a reasonable guess at which ones to start with.Finally, consider a noSQL approach.  And there's a reason why I bring it up last.If you don't attempt any of the above first, your noSQL implementation will perform significantly worse than your existing relational DB solution.  The biggest challenge will be the number of joins that you have in your queries.  While you aren't likely to have a lot of complex joins, noSQL systems run best with little to no joins in the queries.If you reduce your schema as suggested in the first segment, then you may have a decent chance at migrating to noSQL.  I would probably collapse Hotel, Country, Room, and Room_Type into one table though.  That would leave simple joins from there against Hotel_Category and Hotel_Type, but I'm assuming that those two tables are used less often when finding rooms.Along with collapsing the schema, you'll need to index against the major elements that you want to search against.  Perhaps even more so than relational DBs, the noSQL approach relies heavily upon the prebuilt index in order to find the information you need quickly.Bootnotes:As far as which would be faster (relational vs. noSQL), I really don't know and I don't think anyone could know until you've spent some time building and tuning both.  Work on one doesn't apply to the other, so you have to double your effort in order to truly answer that part of your question.  If you're already invested on the relational side, I don't see anything compelling within your question to switch to noSQL.Fuzzy search can be a challenge regardless of underlying database type.  The best thing you can do here is look at the options provided by the platform you pick and start trying to implement fuzzy search.  Profile that; keep revising; and see where your iterations take you.