Database design - question about efficiency (and general design quality).

Question

I fear I don't know what I'm doing.

1:

I have a table called ticket which has a column called total. When the total is updated I want to keep a record of it (old total, etc), so I decided to remove the total column and create a table called ticket_total with columns ticket_id, total, and datetime (the most recent of course is the "current" total).

or

2:

Then I realized that I will later want to give my clients the ability to sort tickets by total, or pull reports that aggregate the totals, etc. So, I decided instead to put back the total column on ticket, and to change the total column directly when the total is updated, but first create a ticket_total row as a record of the previous total.

It seems that version 2 would be highly efficient because I wouldn't need to query the related ticket_total table as much, but I wonder what you DB gurus out there think. I'm just learning database design and fear I'm never going to be good at it.

Answer 1

I would make ticket_total a view, not a table. I would create another view ticket_current where I would filter the tickets by the latest date, that is if the ticket table is dual keyed on ticket_Id and datetime. If not, disregard that last part.

Answer 2

I would go with option 2 that you have suggested.

Just make sure that you are doing the Update (of ticket) + Insert ( in ticket_total ) in a transaction to ensure that the integrity is maintained.

Answer 3

Your second alternative is faster, but if you want to create reports on historic values of the total, you should should have a separate table where you store the value you're replacing together with a timestamp. This type of table is referred to as an audit table.

Answer 4

Re: "efficiency" -- best is to not worry too much about database efficiency at the beginning of a project. Premature optimization is a common mistake to avoid.

Better is to focus on your needs and design to them. Later, you can test to see if where the performance bottlenecks are and work on solving them.

For smaller databases (tens of thousands of rows), even "inefficient" queries often go very fast given todays' servers and software.

Keep it simple I suggest that you avoid combination keys and overloading table semantics unless you really have the need.

Proposal You have two types of data: current ticket information, and a history table for old "total" values.

So your ver 2 would be preferred.

ticket
  id
  field_a
  field_b
  total    # current_total

ticket_total  # history of ticket total field
  id
  ticket_id
  total
  create_time

Also "total" sounds like an aggregation of something. I suggest that you try to come up with a field name that is more descriptive. -- What is the field a total of? "total_worktime" ?

Added Remember to add indexes for the tables. Index by anything that you use for finds. Eg does the ticket table have a customer_id? Be sure that it's indexed.