Adding a sales tax table to an existing database

[Ray-El]

I'm about to perform a major overhaul on an existing database that is used to track customer orders. They want to add a billing feature that automatically calculates sales tax appropriate for each state/zip code on an item-by-item basis, since some of the line items being sold are taxable and some are not.

So the person entering the job will have to determine the taxability of the item and then I can set up the database to apply the applicable sales tax. Or maybe I will set up a table of items with predefined tax statuses. Haven't decided yet.

But before I go creating this myself, I'm wondering if anyone knows of an existing plug-in, or something, that would make calculating and updating state sales tax data easier.

If not, I'm thinking the best way to approach this would definitely be to create a separate table for the sales tax data and set up a relationship between that and a customer info table by state or zip code. Then whenever a line item is entered, a field can be defined to use an IF statement to calculate whether the line item is taxable or not, and if it is, then it can pull a percentage from the tax table by state or zip and multiply it by whatever subtotal cost applies to that particular line item.

One issue I already foresee is that the current database is set up so the customer's city, state and zip code info are all in the same field as the street address. It's entered as one long line of data. I'm going to try to link the database to an SQL server to pull in all the customer address data into separate fields, which I think will make the relationship part of this job work better.

It sounds very easy in theory. Almost too easy! Has anybody ever done something like this before who can offer some suggestions before I dive in head first? Thanks for any tips you can offer!