CODEWITHSUNDEEP
sqldatabase-design
geek_factorial
geek_factorial

Reputation: 85

How can I properly implement commerce data relationships in a sql database?

First, I'd like to start out expressing that I am not trying to just have someone create my table schema for me. I have spent some time weighing the options between the two possibilities in my design and I wanted to get some advice before I go and run wild with my current idea.

Here is my current schema, I will put a ? next to columns I'm considering using.

Key:

table_name
----------
col1 | col2 | col3

tax_zone
---------
tax_zone_id | tax_rate | description

sales_order
-----------
sales_order_id | tax_zone_id (FK) or tax_rate (?)

sales_order_item
-----------
sales_order_item_id | sales_order_id (FK) | selling_price | amount_tax or tax_rate (?)

So, if it wasn't already clear, the dilemma is whether or not I should store the tax data in the individual rows for an order, or use a join to pull the tax_zone information and then do something in my query like (tz.tax_rate * so.order_amount) as order_total.

At present, I was thinking of using the method I just described. There is a problem I see with this methodology though that I can't seem to figure out how to remedy. Tax rates for specific zones are subject to change. This means that if a tax rate changes for a zone and I'm using a foreign key reference, the change in the rate will reflect in past orders that were done with a different rate. This causes an issue because at present I'm using the data in this table to store both orders that have been processed and orders that are still open, therefore if someone were to go re-print a past order, the total amount for the order will have changed.

My problem with storing the specific rate or tax amount is that it means every time someone was going to edit an order, I would have to update that row again with the changes to those values.

In the process of writing this, I'm starting to move towards the latter idea being the better of the two.

Perhaps if someone can just provide me the answer to the following questions so I can go research them myself some more.

Is this a known problem in database modeling? Are there any well known "authorities" on the subject that have published a book / article?

Any help is much appreciated, thanks!

Upvotes: 0

Views: 662

Answers (3)

wildplasser
wildplasser

Reputation: 44240

Well, versioning and history is a well known problem in database modelling. Your solution is very common.

For a simple enumeration like VAT-rates a simple "foreign key tax_id referencing taxtable(id)" will do. The tax-table should never be updated, once a tax_id is enterered, it should stay there forever. If the tax rates are changed at the end of the year, new record should be entered into the tax_table even if records with the new value already exist.

The best search phrase for search engines is probably "temporal database".

UPDATE: http://www.google.nl/url?sa=t&source=web&cd=2&ved=0CCMQFjAB&url=http%3A%2F%2Fwww.faapartners.com%2Fdownloads%2Foverige-publicaties%2Fpresentatie-over-tijd-in-databases%2Fat_download%2Ffile&rct=j&q=veldwijk%20temporal&ei=HQdxTtimCcKr-QansM28CQ&usg=AFQjCNEg9puU8WR1KIm90voSDp13WmE0-g&cad=rja

Upvotes: 1

Ben
Ben

Reputation: 52863

2 points. You're going to have to store the tax rate somewhere or you're not going to be able to add it to sales_order, or anywhere else. Secondly the tax rate can change over time so you don't want to update each time.

So you have two options.

Store tax rate in a reference table and update each order with the correct tax rate at the time of entry into the table.

Calculate everything every time you access it.

Personally I would go for option 1 BUT have a start time as part of the Primary Key in the reference table as if you ever do need to change the tax-rate you may need to know what the correct rate was at the time the order was placed.

Upvotes: 1

chrispanda
chrispanda

Reputation: 3224

In the situation you describe, you will eventually have to store the tax rate in the orders table, because you will need the rate at which the order was closed.

Therefore the cleanest solution has to be to calculate the tax rate each time an order is updated unless it is closed. You could use a trigger to do this.

(Ben's answer popped up as I was writing this - seems we disagree, which is probably not helpful :-)

Upvotes: 1

Related Questions