1

I dont know what type of Audit logging system do I need for my tables design.

Sometime staff change the name of the product, price, options or extras with it. But we don't keep the history what have been updated or deleted.. how to deal with this situation?

Use Audit (history table) or versioning type? Please provide example for my tables design.

A product contain one or more options. An Option can have extras or without extras.

Tables design example:

Product Table:

mysql> select * from products;
+-----------+------------+
| ProductID | Name       |
+-----------+------------+
|         1 | Hard Drive |
+-----------+------------+

Options Table: (There are two options from ProductID 1)

mysql> select * from options;
+----------+-----------+---------+-------+
| OptionID | ProductID | Name    | Price |
+----------+-----------+---------+-------+
|        1 |         1 | Samsung | 55.00 |
|        2 |         1 | Hitachi | 20.00 |
+----------+-----------+---------+-------+

Extras Table: (OptionID 2 have two Extras... OptionID 1 without Extra)

mysql> select * from extras;
+---------+----------+------------------+--------+
| ExtraID | OptionID | Name             | Price  |
+---------+----------+------------------+--------+
|       1 |        2 | 10 Year Warranty | 100.00 |
|       2 |        2 | 20 Year Warranty | 250.00 |
+---------+----------+------------------+--------+

If I change the name of the product (products.name)... should options and extras data (whatever ProductID is linked to) automatically add into history table or use versioning method?

Some product have like 20 options, each option have like 10 extras.

I have over 300,000 rows in the options and extras tables.

I have Order and Order_Items tables.. In the Order_Items table it contain list of OptionID (FK) but I did include the Option Name... So if I change the Option Name (options.Name) then the order will be effected.

user622378
  • 2,318
  • 6
  • 42
  • 63
  • What is the business problem you are trying to solve? Did you want to be able to just view an audit trail/history? Did you want to be able to revert to a previous version of a product? Or did you just want to ensure histortical orders show the correct options/details after updates have been made to the product? – Daveo Jun 22 '11 at 02:09
  • http://stackoverflow.com/questions/6318317/temporal-database-design-with-a-twist-live-vs-draft-rows – Denis de Bernardy Jun 22 '11 at 09:44
  • @Daveo I want to be able to to revert to a previous version of a product / options / extras. I can view what has been changed. – user622378 Jun 22 '11 at 11:09
  • then look at the different solutions available in the URL posted by Denis – Daveo Jun 22 '11 at 12:06
  • @Daveo, I did a bit of research, in fact I do not need to revert to a previous version of a product / options / extras. I like to logs what have been changed/deleted. And ensure the order_items table show the correct options/details.... – user622378 Jun 22 '11 at 12:35

2 Answers2

1

I don't think you've given us enough information to answer your question. Where I work, staff change data in the database every day. We keep history for some tables, and not for others. We keep an audit trail for some tables, and not for others. For some tables, we keep neither a history nor an audit trail.

I can imagine changing a product's name to correct a typo. That kind of change wouldn't usually require any changes to options or extras.

I can also imagine changing a product's name in a way that makes it a different product, and that would probably require changes to options and extras.

Since most companies sell products, I think you should reconsider the names of your tables. Your "products" table seems more like a category of products. Your "options" table seems more like products. Categories don't have prices, but products do.

You also asked about orders. Changes to values in your tables "products", "options", and "extras" should have no effect on previous orders. Five years from now, you might have to go to court and provide a copy of the order. If you're missing the paperwork, your database better be able to reproduce exactly what the customer paid for, even if it's full of typos and wrong prices.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Catcall, I did a bit of research.. What I need whatever I update/soft-delete the product / option or exteas - it should be logged. I dont need revert back to previous version. Is it good to include product name and price fields in the order_items table (items order from customer)? I may have like 5000 orders every day. – user622378 Jun 22 '11 at 13:02
  • *Logged* meaning history or audit trail? History means you store facts like about what the values were at such-and-such a time. Audit trail means you store facts like who changed which values from what to what at such-and-such a time. (And, possibly, from which computer or IP address.) – Mike Sherrill 'Cat Recall' Jun 23 '11 at 01:05
  • Hmmmm, which one do you think it would be suitable for my need? What I thought instead saving/duplicating the product name and the cost in the order table - the productid or order date could look up on the history or audit trail table ... from there we can get the correct price and the name as it was... ? – user622378 Jun 23 '11 at 17:47
  • @user622378: I don't know which is suitable for you. I don't like the idea of looking up product name and cost in an audit table. When the requirements for auditing change, you should be able to disable the triggers that maintain audit trails and drop those tables without affecting any other part of your database. If you're looking up prices or product names in the audit tables, you can't do that. Historical tables are better suited to that kind of thing, but still prone to error. I like the low-risk option best--store the name and price with the order. – Mike Sherrill 'Cat Recall' Jun 24 '11 at 10:29
0

From your comments. I would suggest doing two things.

Adding a Audit Log (history) table for your Product and Category tables. Example here Audit logging for products data?

For the Options/Extra table I would do versioning by having a start and end date on each row, so you can see the value at any given point in time. A good explanation is here versioning each field vs history date field?

Community
  • 1
  • 1
Daveo
  • 19,018
  • 10
  • 48
  • 71
  • Thanks for the answer. In regarding to versioning I don't need to include Option Name, Extra Name and Price in order_item table? I wonder how accurate is a start and end date to find Options/Extra data..? Wouldnt Version Number be better instead of Date – user622378 Jun 22 '11 at 13:13
  • you can use either version number or date. – Daveo Jun 22 '11 at 23:31