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:
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.
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.
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?