Hi,

I could use some help/advice in implementing one of my business requirements. The application is for order processing.

The requirement is as follows: "Any item in the order may be tax exempt, or the entire order itself may be tax exempt. In either case when this occurs a tax exempt ID must be captured."

Here are the tables that seem to be relevant for this requirement:

Order -----> OrderDetail -----> OderDetailTax

An order can have one or many details (items), and each detail have can zero or many taxes. If you need I can get more detailed with these entities but it may not be necessary.
I have a solution to implement this requirement but I am not happy with it. I was thinking of adding two fields to the Order and OrderDetail tables: IsTaxExempt and TaxExemptID. What I do not like about this solution is that without triggers it is not possible to enforce this rule in the database so it is inevitable that eventually there will be a record with IsTaxExempt = true but still there will be records in the OrderDetailTax table. From my experience if it can happen in the DB it eventually will.

Any other approaches on how to implement this requirement is appreciated, I've been thinking it over a few days now and can't come up with anything else.

Thanks

I would add a TaxExemptID column to Order and a IsTaxExempt column to OrderDetail. I see no reason for another table. Here in the USA the TaxExemptID is really an attribute of the customer, it doesn't change from order to order or item to item. Some items are tax exempt (usually those that are being purchased for resale) and some are not (office supplies).

So unless you have a real need to store a unique tax exempt ID per item I see no reason for a special table. Is that a requirement?

Thanks, you solved my problem. I wasn't thinking that it was an attribute of customer which was my mistake. Each order can have multiple customers, it's a restaurant POS ordering system. However each order can also have multiple invoices, but invoices can only have one customer. So I will make it an attribute of Customer and all those customers invoices will inherit tax exemption.

The only problem is it's going to be alot of work for the application to maintain, and there's no way other than triggers to keep it honest. There are a lot of events that could happen which will require going in and recalcualting taxes, i.e. change invoice customer, transfer item to different invoice, etc.