2

Accounting schema design using MYSQL

I excluded datetime and timestamp solution. As two record may be inserted in a millisecond but yet in order.

auto-increment is not good practice as Schema design change in future will break the increment according to SQL Best Practices - Ok to rely on auto increment field to sort rows chronologically?

what I can think of now is

ORDER BY datetime desc, id desc

any suggestion?

Community
  • 1
  • 1
Elliot Yap
  • 1,076
  • 1
  • 12
  • 20
  • `datetime DESC, id DESC` is exactly how to do it. The time is most important, and sub-order on an auto-increment value if it is present now. If you change your schema later and lose it, then you would not be able to sort these, but there really isn't a better way unless you build significantly more logic to increment another column when a timestamp already exists. – Michael Berkowski Jan 01 '15 at 03:53

1 Answers1

0

referring to this post answer: Why doesn't MySQL support millisecond / microsecond precision?

MySQL version 5.6.4:

"MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision."

is microsecond good enough for you? That's 1M rows per sec!

Community
  • 1
  • 1
MrE
  • 19,584
  • 12
  • 87
  • 105