17

I have a log table that captures the datetime stamp of when certain files were exported to another system.

The exportedLog table currently has three fields:

id                (primary key)
messageId         (int)
exportedDateTime  (datetime)

Reviewing this I found that the id field serves no purpose, as there are no joins to this table. The only thing working on this table is the insert of the batch job that processes the messages and inserts into this log table.

Should I remove the id field?

Should I have a primary key on either messageId or exportedDateTime or both?

1

3 Answers 3

13

Should I remove the id field?

I would recommend keeping it.

You may not need the field now, but in the future, it can really help you out -- what if you need to store details of the files for each log entry?

I don't know how large this table will get and how quickly, but adding a column to a large table is typically an expensive operation. If the table is relatively small, then it's not a big deal to keep in terms of storage space. IMO, keep the column and save a potential headache later.

Should I have a primary key on either messageId or exportedDateTime or both?

It doesn't sound like messageId alone would be unique in this table (though I could be wrong), and creating uniqueness on a date/time column alone can potentially create duplicates (and hence errors). The only option left is a 2-column key, which is not particularly appealing given the scenario I set out above.


Essentially, my point of this answer is that keeping the column is not a big deal (I'm assuming), but needing it later may be a big deal and/or require extra work to put it back.

6
  • If you don't have joins on this table, no updates and no deletions, then you don't need keys at all.

  • If this is not the case, and messageId is unique, then you can make it a primary key.

  • If it's not unique, but (messageId, exportedDateTime) is, then make this a composite primary key.

  • If even the (messageId, exportedDateTime) combination can give duplicates and updates and deletes might be needed (say to remove accidentally inserted rows), you'd better leave the id field as is.

0

A Primary Key won't hurt... if you consider the purpose of the log/audit trail, it will probably be used (queried) in the future to resolve an issue or restore data, etc. and will likely be joined to other existing non-log/audit tables to perform that sort of work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.