How can I handle multiple row revisions in a single table?

Question

I'm working on an app where users enter pricing quotes. They want to be able to have multiple revisions of the quotes and have access to all of them to revise and view.

Currently the data is stored in a Quotes table that looks like this:

QuoteID (PK, autonumber) data1, data2, data3 and so on.

QuoteID foreign keys to other tables for one to many relationships for details about the quote.

Is there a way to keep all of the revisions in the Quotes table AND handle revisions? This way, the FK relationships to other tables would not be broken.

Answer 1

You could add a Revision column to both your Quotes table and the other tables making a compound key but that would probably be a bit awkward to keep in sync. I think your best bet is to make the QuoteID column NOT be a primary key and add a new primary key that is used to link the Quotes table to the other tables. The QuoteID then becomes just a field that you can search on (you'd probably want to create an index on it).

Answer 2

Based on what you said and some gueses as to what else/what more you need, I came up with the following table structure outline (tables in ALLCAPS, columns in CamelCase; columns ending in Id are identities or suitable natural keys; where the ColumnId name matches that table name, it's a primary key, otherwise it's a foreign key into the referenced table):

--  CUSTOMER  ----
CustomerId

--  QUOTE  ----
QuoteId
CustomerId
Data1Id

--  QUOTEREVISION  ----
QuoteRevisionid
QuoteId
CreatedAt
Data2Id
Data3Id

--  DATA1  ----
Data1Id

--  DATA2  ----
Data2Id

--  DATA3  ----
Data3Id

CUSTOMER records who can make quotes.

QUOTE tracks a customer's pricing quotes. One row for every given [whatever] that they're entering quotes for.

QUOTEREVISION records each quote revision they enter. When a Quote is first created, the first QuoteRevision will also be created. CreatedAt would be a dateimte, to keep track of when they occured. QuoteId + CreatedAt is the natural key for the table, so you might not need QuoteRevisionsId.

DATA1, DATA2, DATA3, and others as needed contain the extra information. I configured Data1 to hold information relevant to the quote level--that is, the same fact would apply to each quote revision. Data2 and Data3 would contain data that could vary from revision to revision.

I've no doubt there's stuff in here that doesn't apply to your problem, but hopefully this gives you some ideas for possible solutions.

Answer 3

I agree with the design of Philip Kelley, I may only notice that quote revision you can calculate in the output using ROW_NUMBER() or it emulations according to your DBMS.

There is also a nice book about storing historical data: http://www.cs.arizona.edu/people/rts/tdbbook.pdf