NHibernate-friendly database design for dynamically recreated tables

Question

Hi,

I have an application design question for the nhibernate gurus our there. My application tracks a list of documents submitted and displays data parsed from those documents to the public as a searchable transaction list.

The people/entities submitting these documents may (at any time) replace a previous version of a document with an updated version. Only transactions from the most recent version of each document should be displayed in the searchable transaction list.

The front end of this application uses NHibernate for data access to perform the display and search of the transactions.

My initial idea is to put a trigger on my table of documents that will drop and recreate the table of transactions after any insert, update, or delete of the documents table using a stored proc.

There seem to be two shortcomings with this idea:

1) Since the stored proc will take maybe a minute to run to regenerate the transaction list, there will be a period of time where the transaction list table will be incomplete or not even exist.

2) The data that NHibernate will be reading will become invalid after a transaction table rebuild, and any cached information will be invalid.

A few alternatives that I have contemplated are:

1) using Views as the 'tables' NHibernate uses to read from the database, and update those views to point to the new data tables after a successful rebuild is complete. This doesn't resolve the possible caching problem, though.

2) do the transaction table rebuild through the NHibernate session, though I can't figure out how the mechanics of this would work. NHibernate would need to know about the 'live' set of transactions as well as the 'pending' set of transactions, and the pending set of transactions would need to be mapped to some dynamically named table(s).

Maybe I'm just architecting myself into a pretzel.

Anyone have any ideas what the NHibernate best practices for this sort of problem would be?

Answer 1

I recommend to use Lucene.net for indexing and searching that documents. And you don't need in a trigger and any SP - Lucene will update all indexes automatically.
And here are some tutorials about lucene:
http://stackoverflow.com/questions/1002255/lucene-net-best-practices
http://stackoverflow.com/questions/636579/what-are-some-good-resources-on-using-lucene-net
http://www.lucenetutorial.com/lucene-in-5-minutes.html (it's for java version but the API is the same)