1 22 23 24 25 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 7:26 AM by Alessandro Rossi Go to original post RSS
      • 360. Re: Treatment of zero-length strings as NULLs?
        William Robertson
        So as well as CUSTOMERS_WHO_DONT_HAVE_PHONES_BECAUSE_THEY_DONT_LIKE_THEM we would also have CUSTOMERS_WHO_DONT_HAVE_PHONES_BECAUSE_THEY_HAVE_ACCIDENTALLY_DESTROYED_THEM and so on, and the truly relational database would have some way of managing the exclusivity relationships and presenting the results. Perhaps these could all be stored in something like a cluster for fast joins.

        However, turning a reason code into multiple tables is arguably a denormalisation, and means a schema change is required if we decide to add CUSTOMERS_WHO_DONT_WANT_TO_REVEAL_THEIR_PHONE_NUMBERS.

        This is the sort of practical problem the relational theorists expect the vendors to solve magically.
        • 361. Re: Treatment of zero-length strings as NULLs?
          537882
          Yes of course, but in this way it is only necessary to model granularity of the facts in that you have the interest. If I am TelCel Marketing for instance, then as my core business I will be interested in many details of customers and their phones and such reasons why there is no number. On the other hand if I am Oracle Support then knowing that the customer doesn't have a phone, won't tell me it number, or doesn't like phones then is irrelevant and all I'm interested in is use email instead.

          However, turning a reason code into multiple tables is arguably a denormalisation, and means a schema change is required if we decide to add CUSTOMERS_WHO_DONT_WANT_TO_REVEAL_THEIR_PHONE_NUMBERS.

          I believe it is more aguably normalisation using join dependency (rather than normalisation using functional dependency)

          My point was just indicate that this was a taught method, not that it is a certain choice for Oracle implementors everywhere. I believe it is preferable to Albert's methodology.

          Counter argument is that normalisation proceeds on facts, and not on entity. Which is where you argue denormalisation probably.

          Message was edited by:
          The Flying Spontinalli
          • 362. Re: Treatment of zero-length strings as NULLs?
            537882
            This is the sort of practical problem the relational theorists expect the vendors to solve magically.

            Is it better or worse than the practical problems of NULL that vendors can't solve at all?
            • 363. Re: Treatment of zero-length strings as NULLs?
              521710
              dummy@ora10ee> select NULL from dual;
              
              N
              -
              
              
              1 row selected.
              • 364. Re: Treatment of zero-length strings as NULLs?
                BluShadow
                And what does that show Dummy?

                btw, An IQ test is only a measure of how well a person is at taking an IQ test, it cannot truly measure intelligence.

                ;)
                • 365. Re: Treatment of zero-length strings as NULLs?
                  William Robertson
                  > I believe it is more aguably normalisation using join dependency (rather than normalisation using functional dependency)
                  Counter argument is that normalisation proceeds on facts, and not on entity. Which is where you argue denormalisation probably.

                  I'm afraid I don't know what those mean. I was thinking that implementing n possible reasons for something by defining n identical child tables seems like a denormalisation to me, and makes it harder to write GROUP BY queries summarising no-phone reasons. If that's called normalisation using join dependency, well fine.

                  > Is it better or worse than the practical problems of NULL that vendors can't solve at all?

                  I'm not convinced it's a solution, and in fact I suspect the problem of NULL, and indeed Codd's requirement that nulls should be handled consistently, are unsolvable anyway at some Godelian level. On the other hand this could be why I'm a database programmer and not a mathematician or a relational theorist.

                  btw it's a pleasure to be able to discuss this stuff with someone who is prepared to enter into a debate, rather than just insulting anyone who disagrees with him and repeating what he said earlier.
                  • 366. Re: Treatment of zero-length strings as NULLs?
                    521710
                    And what does that show Dummy?
                    Isn't that obvious?

                    Selecting NULL resulting with one empty row

                    Result is something empty and that's NOT NULL

                    NULL is nothing and one empty row is something else then nothing :P
                    btw, An IQ test is only a measure of how well a person is at taking an IQ test, it cannot
                    truly measure intelligence
                    Correct.

                    Message was edited by:
                    Dummy
                    • 367. Re: Treatment of zero-length strings as NULLs?
                      William Robertson
                      NULL is something empty and that's NOT NULL

                      NULL is nothing and one empty row is something else then nothing :P


                      I don't see what that is supposed to prove. Is this some sort of null-is-not-null argument? We'll be back on bloody Aristotle if we're not careful.
                      • 368. Re: Treatment of zero-length strings as NULLs?
                        521710
                        I don't see what that is supposed to prove.
                        Nothing to prove - just one dummy observation.
                        Is this some sort of null-is-not-null argument? We'll be back
                        on bloody Aristotle if we're not careful.
                        Eee, my Willy - if we're not careful again will come moody Billy
                        • 369. Re: Treatment of zero-length strings as NULLs?
                          6363
                          Selecting NULL resulting with one empty row
                          The query returns null for each row in the table.

                          Dual has one row.

                          You got one null back.

                          If you want no nulls, query an empty table if it confuses you less.
                          • 370. Re: Treatment of zero-length strings as NULLs?
                            APC
                            We'll be back on bloody Aristotle if we're not careful.
                            Did somebody mention Aristotle? That sounds like the cue for a song.

                            Cheers, APC
                            • 371. Re: Treatment of zero-length strings as NULLs?
                              521710
                              Selecting NULL resulting with one empty row
                              The query returns null for each row in the table.

                              Dual has one row.

                              You got one null back.

                              If you want no nulls, query an empty table if it
                              confuses you less.
                              Brilliant thoughts 3360 - enlightening my mind :P

                              3+3+6+0 != 3+3+6+NULL
                              =>
                              My next dummy-question is:
                              Why NULL killing numbers and not a string?
                              • 372. Re: Treatment of zero-length strings as NULLs?
                                537882
                                I'm afraid I don't know what those mean. I was
                                thinking that implementing n possible reasons
                                for something by defining n identical child
                                tables seems like a denormalisation to me
                                I don't think it's so much matter of normalisation but of orthogonality as I mention already. You end up ultimately with two ways to project the set of customers, either project id from customer, or project customer_id from union all of all reason not to have phone number tables, assuming all reasons are have mutual exclusion and that there is no customer such that they have no phone, had one but lost it, dont want a new one and wouldn't tell the number even if they had.
                                • 373. Re: Treatment of zero-length strings as NULLs?
                                  537882
                                  A more interesting question is why this return only one row...

                                  create table a(x number);
                                  create table b(y number);
                                  insert into a values (null);
                                  insert into b values (null);

                                  select x from a
                                  union
                                  select y from b
                                  /
                                  • 374. Re: Treatment of zero-length strings as NULLs?
                                    APC
                                    Why NULL killing numbers and not a string?
                                    Because addition is different from concatenation.
                                    '3'||'3'||'6'||'0' != 3+3+6+0
                                    Cheers, APC