Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which DBMS is this for? –  ypercube Oct 9 '12 at 17:26
add comment

2 Answers

up vote 6 down vote accepted

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.

share|improve this answer
add comment
  • 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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