Database Design Advice
diment at gmail.com
Sat Nov 9 20:34:03 GMT 2013
On 10/11/2013, at 4:24, Peter Corlett <abuse at cabal.org.uk> wrote:
> On 8 Nov 2013, at 19:33, David Cantrell <david at cantrell.org.uk> wrote:
>> Because you might need to know which of two events at 2013-11-08Z19:31:04 happened first. Sure you could use microseconds or whatever to get better resolution, but all that does is make the problem less likely, it doesn't make it go away. You also normally want sort order to be consistent. If you have two records where the sort field is the same, the order they come out is going to be unpredictable.
> That's just a specific example of the general problem where one desires a stable sort by a non-unique column. The simple solution is to just add more columns to the ORDER BY until the tuples *are* unique. The primary key is an obvious choice of tie-breaker if you don't care about the order so long as it's consistent.
Also, the time I tried to use a date field for ordering, it wasn't susceptible to this problem for reasons related to the specific use case. It was still a bad idea though, for scope-creep related reasons.
More information about the london.pm