In an integer column in a relational database, what is the alternative of having null?
0 is not an option, as that will return incorrect mean/median calculations. So what would be there? Or am I misunderstanding?
0 is not an option, as that will return incorrect mean/median calculations. So what would be there? Or am I misunderstanding?
If you do anything else, such as using nulls, then you are just creating a problem that WILL come back and bite you in the nether regions of your psyche.
Of course, there will other opinions about how to handle this. As far as I am concerned, Nulls are a curse foisted on us by those vendors and standards bodies who took the the easy way out.
That will create real problems and bite you immediately.
When nulls are stored there are no guarantees that anything you ask of the database will ever turn out right. Especially when there are millions of records stored. Two queries that should give you the same answer give different results when nulls exist. Seen it too often.
I have also worked for companies that didn't use relational database theory for their products and they had far more issues. In a couple, I was able to hive off the database designs from the main systems and got the applications to actually work and work properly.
Ideally, your database system would support tagged unions, and have a strong type checker. Then, you may have a column of type 'Maybe Int'. If you want to take the median, the median function would be of type 'Agg Int Int' (or something like that), so you wouldn't be able to use this column directly as an input. Instead, you'd have to only supply rows whose values have been checked to be 'Just'.
So
CREATE TABLE MyTable ( group TEXT, myColumn MAYBE INT )
SELECT group, MEDIAN(myColumnValue) FROM MyTable WHERE Just MyColumnValue = myColumn GROUP BY groupI wonder, if one were to query the DB, what would show up in the "birthdate table" column? I'm kind of hoping it would be zero, which would in turn be interpreted as the epoch, but sadly it would probably be null...
Not deliberately, perhaps reflexively. It just seemed the most straightforward (and interesting) interpretation of the comment in question. I hadn't seen the other comments.
> I wonder, if one were to query the DB, what would show up in the "birthdate table" column?
As I understand it, what would show up in the "birthdate table" column is a table. You would have to do a select on that table to get actual data out, and in that select you'd get either zero or one row returned.