In other words, perform the positives over-shadow the disadvantages?

The aim is to buy the N newest records.

pros:

  1. Do not have to index the produced_at column
  2. Your ORDER BY is going to be utilizing a clustered index

cons:

  1. You are depending around the time purchased character from the primary answer to hold true for that existence from the development cycle

Ideas?

Because of database transactions, multiple periods may commit at different occasions, you will notice that it's not always time purchased.

Another disadvantage by not developing a index on created_on you didn't remember to say:

  • You can't search or filter by specific date/time or perhaps a range.

I believe this are only able to be clarified with respect to the needs.

Although it's quite common practice, you will find lots of cases when the main key become unordered. I have frequently encounter this issue myself. Personally, i think it is better to set produced_at column that's set during the time of creation.

Unless of course you serialize all of your card inserts there's most likely not a way to ensure the order from the auto-incrementing secret is chronological.

If you are using a surrogate key to look for the sequence and drive business logic from this then you definitely also undermine the main benefit of getting a surrogate key to begin with. The surrogate should really don't have any business meaning, to ensure that for instance that you could easily update/reallocate the values throughout database maintenance and schema changes.