This discussion is archived
1 22 23 24 25 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 360. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    dummy@ora10ee> select NULL from dual;
    
    N
    -
    
    
    1 row selected.
  • 364. Re: Treatment of zero-length strings as NULLs?
    BluShadow Guru Moderator
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    > 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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Why NULL killing numbers and not a string?
    Because addition is different from concatenation.
    '3'||'3'||'6'||'0' != 3+3+6+0
    Cheers, APC