5 ms·

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 calculatio

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?

deleted 8y ago
[deleted]
oldandtired 8y ago
If there is missing data to occur within a column, then pragmatically, you split that column off into its own table with the associated key from the original and store only those keys that have valid values. 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.
- bvrmn 8y ago
  > you split that column off into its own table with the associated key from the original and store only those keys that have valid values. That will create real problems and bite you immediately.
  - oldandtired 8y ago
    Demonstrate that it will bite you immediately. This technique has ensured that only valid data has been kept and that reporting works. 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.
    - bvrmn 8y ago
      Table separation leads to record fragmentation and big IO cost on record fetching. Your ideal academic world will be crashed under production reality. "Millions of records". Bwa-ha-ha.
      - oldandtired 8y ago
        If your DBMS is so poorly written that record fragmentation is an issue then you need to change the DBMS. Since most (>99%) of the database design and implementation work that I have been involved since the mid-80's was business related and for a variety of different industries, I didn't find table separation to be a problem. The appropriate designs led to faster applications. 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.
tome 8y ago
Use an "Optional<integer>" column instead. Doesn't you RDBMS have the notion of "Optional"? There's the disaster!
- ErwinSmout 8y ago
  Truly relational systems support relation-valued attributes and those are the perfect means to support Optional<anything> in a database.
  - jessaustin 8y ago
    What does a "relation-valued attribute" have in e.g. the "birthdate" column when we don't know the customer's birthdate? Is it... null?
    - dllthomas 8y ago
      IIUC, the parent is suggesting that you have a user table with a birthdate column which is itself a table with either no rows or one row.
      - jessaustin 8y ago
        I suspect you've done a bit of steelmanning here. Thanks though, as parent had been unable to come up with this short explanation despite writing a dozen vague-but-vitriolic posts. Perhaps he'll take note for next time he crawls out from under his rock. I 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...
        dllthomas 8y ago
        > I suspect you've done a bit of steelmanning here. 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.
tathougies 8y ago
> 0 is not an option, as that will return incorrect mean/median calculations. So what would be there? Or am I misunderstanding? 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 group

deleted 8y ago

[deleted]

oldandtired 8y ago

If there is missing data to occur within a column, then pragmatically, you split that column off into its own table with the associated key from the original and store only those keys that have valid values. 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.

bvrmn 8y ago
> you split that column off into its own table with the associated key from the original and store only those keys that have valid values. That will create real problems and bite you immediately.
- oldandtired 8y ago
  Demonstrate that it will bite you immediately. This technique has ensured that only valid data has been kept and that reporting works. 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.
  - bvrmn 8y ago
    Table separation leads to record fragmentation and big IO cost on record fetching. Your ideal academic world will be crashed under production reality. "Millions of records". Bwa-ha-ha.
    - oldandtired 8y ago
      If your DBMS is so poorly written that record fragmentation is an issue then you need to change the DBMS. Since most (>99%) of the database design and implementation work that I have been involved since the mid-80's was business related and for a variety of different industries, I didn't find table separation to be a problem. The appropriate designs led to faster applications. 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.

tome 8y ago

Use an "Optional<integer>" column instead. Doesn't you RDBMS have the notion of "Optional"? There's the disaster!

ErwinSmout 8y ago
Truly relational systems support relation-valued attributes and those are the perfect means to support Optional<anything> in a database.
- jessaustin 8y ago
  What does a "relation-valued attribute" have in e.g. the "birthdate" column when we don't know the customer's birthdate? Is it... null?
  - dllthomas 8y ago
    IIUC, the parent is suggesting that you have a user table with a birthdate column which is itself a table with either no rows or one row.
    - jessaustin 8y ago
      I suspect you've done a bit of steelmanning here. Thanks though, as parent had been unable to come up with this short explanation despite writing a dozen vague-but-vitriolic posts. Perhaps he'll take note for next time he crawls out from under his rock. I 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...
      - dllthomas 8y ago
        > I suspect you've done a bit of steelmanning here. 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.

tathougies 8y ago

> 0 is not an option, as that will return incorrect mean/median calculations. So what would be there? Or am I misunderstanding? 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 group