Databases should have some built-in “virtual” tables filled with universal sequential data, against which you can join during queries. For instance, there should be a table called “dates” which is just a single-field table with a sequential list of dates, stetching into eternity in either direction.
How would you use this? Say I wanted to get a list of the number of comments posted every day on this blog. I could just use COUNT and GROUP BY to roll them up, but if there were no comments on a particular date, then that date just doesn’t show up (since there’s noting to count).
To get a “0” on date where there were no comments, I would need to join my original query on a table of dates. As it sits, I need to create this table and populate it and maintain it myself. Why couldn’t this be built into a database platform?
There should be a table for numbers too. I was thinking about letters of the alphabet (but then you get into localization issues), days of the week, and months of the year. However, you can create those and maintain them easily, since they’re finite. Dates and numbers stretch into infinity in both directions, so they’re harder to deal with.
Does something like this exist on any platforms that you know of?