4 ms·

Mmm, not sure about some of the complains... - You can make case insensitive searches on the DB using regexes (http://www.mongodb.org/display/DOCS/Advanced+Que

Mmm, not sure about some of the complains... - You can make case insensitive searches on the DB using regexes (http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-RegularExpressions http://www.mongodb.org/display/DOCS/Advanced+Queries#Advance...). A simple case-insensitive regex is not very bad performance-wise, but in general, case-insensitive searches should be avoided for search purposes (you can normalize to set everything to lower case or other equivalent trick) - The proper way of doing an audit (and search later) is to make an independent collection with a reference to the other(s) document in a different collection. Then you can index by user, date, or any other field and leave the main collection alone. The described embedded access collection doesn't look very scalable. - Making map-reduce queries is tricky (at least for me). I think the guys on 10gen realizes that and the new aggregation framework is a way of dealing with this. Anyway, the main advantage of SQL is this kind of things, the rich query capabilities. Even if MongoDB allows some compared with other NoSQL DBs, if there is a lot of work in defining new queries, probably a SQL DB is the best fit, as that is where SQL excel. I don't truly believe in this "you should research everything before starting" (I mean, I believe in research, but too many times the "you should do your homework" argument is overused. Sometimes you make a decision based in some data that changes later, or is incomplete), as there are a lot of situations where you find problems as you go, not in the first steps. But, according to the description, looks like PostgreSQL is a better match and the transition hasn't been too painful, so we can classify this into "bump in the road"/"success history". Also, probably right now the DB schema is way more known that in the beginning, which simplifies the use of a relational DB.

datasage 14y ago
- The proper way of doing an audit (and search later) is to make an independent collection with a reference to the other(s) document in a different collection. Then you can index by user, date, or any other field and leave the main collection alone. The described embedded access collection doesn't look very scalable. I think this point is very important even in the RDBMS side. There are cases, even with relational datastores that would preform better if the dataset was built to the query. The difficulty comes into play when you are trying to keep the denormalized data up to date based on changes within the base dataset.
- einhverfr 14y ago
  > "I think this point is very important even in the RDBMS side." This is true. After all you can't extract an audit trail from a deleted record. Simply in terms of information management it often makes sense to represent this as separate info. > "There are cases, even with relational datastores that would preform better if the dataset was built to the query." The big problem with doing it that way is that you are screwed as soon as requirements change. I recently blogged about not-1NF designs in PostgreSQL (nested data structures for subset constraints), and the lesson I took away was that you really don't want to have your select queries hitting the same tables you maintain for inserts. You want your queries hitting a normalized data structure even if the data comes in as something different.

datasage 14y ago

- The proper way of doing an audit (and search later) is to make an independent collection with a reference to the other(s) document in a different collection. Then you can index by user, date, or any other field and leave the main collection alone. The described embedded access collection doesn't look very scalable. I think this point is very important even in the RDBMS side. There are cases, even with relational datastores that would preform better if the dataset was built to the query. The difficulty comes into play when you are trying to keep the denormalized data up to date based on changes within the base dataset.

einhverfr 14y ago
> "I think this point is very important even in the RDBMS side." This is true. After all you can't extract an audit trail from a deleted record. Simply in terms of information management it often makes sense to represent this as separate info. > "There are cases, even with relational datastores that would preform better if the dataset was built to the query." The big problem with doing it that way is that you are screwed as soon as requirements change. I recently blogged about not-1NF designs in PostgreSQL (nested data structures for subset constraints), and the lesson I took away was that you really don't want to have your select queries hitting the same tables you maintain for inserts. You want your queries hitting a normalized data structure even if the data comes in as something different.