>All,
>
>I am working on a rewrite of a fat client app to thin client.
>
>Our current database design is pretty normalized. It takes as many as 36 tables to store a 'document' (these are health care claims) which must be saved in a transaction.
>
>Contention and deadlocks have only been a minor issue with the fat client since there are typically less than 10 users on the system.
>
>The new system will typically have ~600 simultaneous users and our POC testing has shown that we are going to experience contention issues.
>
>I am exploring a design which will use a single table to hold 'document' information, with columns to hold 'document' metadata and the document data stored in a blob or something similar as XML.
>
>Our app does not manipulate the data in the 'documents', and we do not do reporting on the data in the 'documents'. The 'document' needs to be available for edits and it needs to be available to the user for 13 months.
>
>Presentation layer is ASP.Net. VB.Net for business and data layers.
>
>Does anyone have any opinions or thoughts on such an architecture and its impact on performance?
>
>Thanks.
>
>Bruce

Hi Bruce.

IMO storing a static XML document in a blob is a real waste of SQL Server resources. It would be much better to leave the data as a well formed XML document and reference it by filename in the database. Maintenance is easier and managers love to see their precious data as a file that they can double-click and open (read-only!) in explorer.

Plus the .NET XML classes are easier to use with XML files. You don't have to create Readers and Streams by hand with a stand-alone file.

You can use file attributes or a column in the table to lock the document for a user. And you will need a timer-based process that unlocks documents that are left hanging by clients.

The downside is that NTFS permissions come into play. If you are using identity impersonation, someone will have to maintain the ACLs of the folder(s) where the documents are stored. If you are using a single web application account, then the security becomes much simpler.