Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am in the middle of design an application to issue and store invoices for an organization. The problem is the organization is not stable at all. There are many types of invoices and they may increase and change.

First, I tried to use tables in my DAL, one table to store invoices, one for invoice fields, and one for invoice field values. The problem was, this way needs Reflection to detect fields later, and this may slow the application when invoice contains plenty items.

Second, I tried to keep core of invoice data as two tables: invoices and invoice items. Other fields are fully catchable from other tables. I mean, Business Layer should provide different results per invoice type. It should choose the right queries, and process the results according to the type of invoice. Two problems with this solution are:

  1. I still have lots of join between other tables when I want to show an invoice, for per invoice item. I should recalculate everything every time for every invoice item.

  2. What if the invoice items table doesn’t support a new type of invoice? Then I probably should add a new table to store that kind of invoices items.

  3. My client asked me to keep all data related to an invoice, they do not want me to recalculate it every time. They want something like a snapshot of related data at the time invoice is creating.

Now, what I’m think can I use the benefit of XML in table? I can save the invoice, with whatever fields as xml.

  • I can save the different version of their invoices.
  • On changes, I only update my Business.dll and no DAL change required.
  • Linq to XML is not slow.

What do you suggest?

share|improve this question

5 Answers 5

Overall this is a good plan. Implemented a few things that are remarkably similar in my day. If I were starting design of something like this today I would strongly consider using a document database rather than SQL but that die may already be cast.

If you are using SQL, I would advise pushing as much data outside of the invoice and into the table -- it is loads easier to query on and understand on the fly. Stuff inside XML is a bit trickier to get at.

One thing you posted deserves a bit of discussion:

On changes, I only update my Business.dll and no DAL change required.

Data format or schema changes within the invoice are probably the hardest thing to deal with here. Presuming you are using XmlSerialization, changes to the underlying object can result in crashes at best or silent data corruption at worst. You really need to build in an invoice schema versioning plan from day zero. Exactly what to do depends in requirements, and with XML it is pretty easy to modify the underlying data without hydrating objects, but you will need to do something at some point if this system is expected to live for any significant length of time.

share|improve this answer
    
I didn't consider any schema because invoice format is not fixed. –  Reza Owliaei Feb 8 '12 at 6:38
    
I didn't mean schema in the traditional XSD sense but rather the logical layout of the data file -- even if you don't have a formal schema, some parts of your program probably make some presumptions about the serialized representation of your invoice. You need to account for your program morphing while the data might not have morphed. –  Wyatt Barnett Feb 8 '12 at 15:55
    
You said:"document database rather than SQL". Do you mean Document Oriented Database? such as mongoDB? –  Reza Owliaei Feb 8 '12 at 17:58
1  
Yes, I'd probably prefer ravendb or couchdb over mongodb though. –  Wyatt Barnett Feb 8 '12 at 18:07
    
what about the performance of these dbs vs. SQL Server XML data type. I should query over millions of invoices and performance is very important. –  Reza Owliaei Feb 8 '12 at 19:17

i would suggest you to stick XML for such highly unpredictable and unstructured architecture. A web based solution's to your problem can be seen at http://www.oreillynet.com/xml/blog/2008/05/xrx_a_simple_elegant_disruptiv_1.html this wouldn't be a plugin solution for you problem. but you can see in the above link how this problem can be tackled elegantly. this would definitely help you the architecture your database.

share|improve this answer

I would suggest using superset of all the field which can be there in invoices. Say invoice1 has field A,B,C and invoice2 has A,B,D then have a table which has column A,B, C and D(assuming there are only two type of invoices). And depending upon the columns for which data is available you can create invoice1 or invoice 2.

share|improve this answer

First I would suggest using dynamic types (such as ExpendoObject) to represent the unstructured data in you business layer. This would mean less custom classes.

Second I would recommend using json instead of xml since it would take up much less space in the database/storage engine.

Thirdly, have you considered using a nosql storage solution for the invoices? It's much more suited to less structured data than normal sql.

share|improve this answer

I have tried something similar and been caught out!

In my case the system proved a little too successful, and now stores 2.5 million transactions each with a blob of Xml data. The size of the database is now around 25Gb, and backups are starting to take a long time.

The main problem is that Xml in databases doesn't compress well.

I'm thinking of a redesign now, where the Xml documents are stored on the file system. They're transactional in nature, e.g. write-once, read-many and never update.

Xml files on the file system can be compressed or archived as needed.

There are some considerations. My servers use NTFS, so even the smallest file occupies 4kB of diskspace (compression will help with that). Security and backups also need to be considered.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.