>> If I do switch to RANGE types, I think [2,2] would make sense in this > case. >> Using unbounded ranges might make sense if I wanted to express something >> like "Use up to 1 cup of flour" or "You'll need at least 3 cups of water". >> > > In these cases: > > Flour: [0, 1] - "optional, but maximum of 1-cup"; you cannot supply a > negative amount of ingredient... > Water: [3, ) - "at least three cups, but remember you will have to pay the > water bill at some point..." > > The water example implies some need to flag the quantity as needing an > explanation. In this case I would probably say "[3,3]" and set a flag so > that you can describe, in words, those situations where additional water > would be OK. Mabye some kind of "quantity_precision" flag with possible > values of [EXACT,APPROXIMATE,MINIMUM,MAXIMUM]. The reason I say this is > because the value of storing an amount as a value is that it can be used in > calculations and it is difficult to use infinity in calculations. By > storing a finite range you can more readily use the values in calculations > and when you care about whether the value is "required" or "suggested" you > have a flag you can query to tell you which it is. Yea, I wrote a blog post on this subject recently if you're interested. http://blog.kitchenpc.com/2012/06/27/3-4-things-left-to-do/ The gist is that I needed to store unknown amounts (such as "pepper to taste") as well as ranges of amounts. Trying to represent that in Postgres indeed brought up some controversial design decisions, which led to this StackOverflow question (which had a lot of DB purists yelling at me!) http://stackoverflow.com/questions/11439796/how-can-i-encode-two-numbers-in-a-single-integer Really, the only thing I do with the data is total up amounts to generate a shopping list. With ranges, I simply take the high amount. If one recipe calls for 2-3 eggs, and another calls for 4 eggs, I would add 7 eggs to the shopping list as that's the most you'd need to buy. The "Low" amount is simply for display purposes only, I do no math with this. However, I can't predict what the future holds for the site. Maybe I'll need to store all sorts of crazy things, so it's best to handle things in the best way up front so I don't have to rip apart all this low level code later on. Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
