Reference docs - how many references is too many?


#1

We are working on moving one of the database tables in our application to couchbase. One of the issues is that some of the legacy business logic needs to look up our entities by non-primary keys, and these secondary keys are small in number (by way of example, if the entity is “car” and the primary key is the car’s VIN number, a secondary key might be the color of the car.) So a reference document that lists all “red” cars will have to contain large numbers of primary key values. This leads to efficiency concerns when making mass state changes. Again using an automotive example, say we had an operation that painted all of the cars on our lot black at once. The ref doc containing each color will suddenly face a lot of near simultanous deletions, while the “black” ref doc will get a lot of rapid adds.

So we are wondering if there is an alternative that avoids contention issues (if say we believed that we absolutely needed data integrity in these cases). In our present case, we are already reconsidering as we had originally implemented these examples using views, and that alternative is looking more attractive now.


#2

Sounds like you have started to do this through Views which would help instead of trying to maintain your “red car” index yourself but you will have an easier time doing this through N1QL with 4.0:
you can create indexes such as :

CREATE INDEX red_cars on bucket(color) where color="red" AND document_type="cars";

or

CREATE INDEX red_cars on bucket(color) where document_type="cars";

and write queries like:

SELECT * FROM bucket WHERE document_type="cars" AND color="red";

that will use the indexes defined above. you can download 4.0 here: http://www.couchbase.com/nosql-databases/downloads#PreRelease


#3

Do you have any other options that don’t require prerelease/BETA libraries? I suspect that the answer will be that we won’t want to adopt a solution that isn’t based on a released build.


#4

I understand. What is your timeline? 4.0 is about to be pushed out the door in a few weeks. Would that work?


#5

It might in the future but not right now - we are targeting moving some changes into a release at the end of September, and that is probably cutting it too close. We have already had some discussions where I think we can live with views for our particular scenarios for september, and maybe revisit QLs sometime after that.


#6

Thanks in that case views is the best bet for this.