History Table Design Issue

Hi all,

this is more of a design issue for a History table.

Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table.

Regards,

General Problem

As a general rule of thumb, EVERY table should have a primary key.

I'm sure that there are exceptions (it's a scary world out there!), but the design assumption should be "A primary key is required!" until proven otherwise.

As for foreign keys, that's a different issue.

Let's say you have an employee table.

If you set up an FK from your history table to your employee table, you will not be able to delete an employee from the employee table as long as you maintain those history recorrds.

If you intended for the employee table to be defined as "A list of current employees of the business.", you have just changed the definition to "A list of current and past employees of the business."

That may, or may not, be what you want!

Now, if the primary key of the employee is a meaningless number, and you need to know which employee did that transaction, you might be stuck with keeping past employees in the employee table. Or, of course, you could add the employee ssn and name to the history table. If you used a natural key (like their SSN), then someone could always look it up in the paper trail. Lots of options here!