David suggested using a guesstimate default date along with a boolean to indicate when you're using guesstimates. I think this is a solid approach, but if the default expected_by idea doesn't work for you, a boolean would still make this a lot easier on the Rails side.
It sounds like you're using a setup for Heroku, so I checked the postgreSQL 8.3 manual--a boolean is 1 byte<http://www.postgresql.org/docs/8.3/interactive/datatype-boolean.html>. If this isn't for Heroku, other postgreSQL version probably implement booleans the same way. Your database size should go up by # records * 1 byte + indexing overhead. Though I don't know how many records you're working with, this seems relatively cheap given that it will make your code more readable ("if expected_date_estimated?"). It should also simplify any remaining code you have to write, as you won't have to think about writing elaborate "if" or "case" statements to determine if expected_by was explicitly set. On Thu, May 12, 2011 at 1:06 PM, James B. Byrne <byrn...@harte-lyne.ca>wrote: > > On Thu, May 12, 2011 15:51, David Johnston wrote: > >> > >> +Infinity was chosen as a default to avoid the complexities of > >> dealing with NULL logic in SELECTS. I suppose that the simplest > >> solution is to go with a date of 9999-12-31 and treat that value > >> like infinity. > > > > The "just make it work" solution has many merits - I would > > also probably just use 9999-12-31 as a close approximation > > for +infinity; which itself is just there because you are > > avoiding "estimate is unknown". > > > > Why bother updating the "expected_by" value once the conveyance > > is no longer pending? Do you not really care if something > > arrived early? Even if you do not currently it seems a waste > > to throw out the data when you can readily get the same result > > as-needed (CASE WHEN expected_by <= arrived_at THEN arrived_at > > ELSE expected_by END) without giving up the ability to calculate > > > The main reason to update expected_by is that sometimes the > conveyance arrives without the expected_by ever being set. Leaving > the expected_by value at infinity, or 99991231, or NULL, complicates > other parts of the system. However, leaving untouched expected_by > values that are less than the infinite value is doable and is a > better approach. > > > -- > *** E-Mail is NOT a SECURE channel *** > James B. Byrne mailto:byrn...@harte-lyne.ca > Harte & Lyne Limited http://www.harte-lyne.ca > 9 Brockley Drive vox: +1 905 561 1241 > Hamilton, Ontario fax: +1 905 561 0757 > Canada L8E 3C3 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
