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
  • 345. Re: Treatment of zero-length strings as NULLs?
    APC Oracle ACE
    Currently Being Moderated
    Defining domains in Designer doesn't actually do much in the way of enforcement, even if we use the Table API generators to produce those grisly interfaces. Perhaps they have got better since I last used Designer.

    A proper domain would allow me to define a rule that prevents a column of domain MONEY being added to a column of domain QTY. However, it would allow the two columns to be multiplied together to produce a total cost (also of domain MONEY). Maybe that's too close to magic. But it would be nice.

    Cheers, APC
  • 346. Re: Treatment of zero-length strings as NULLs?
    BluShadow Guru Moderator
    Currently Being Moderated
    What it needs is a business engine alongside the PL/SQL and SQL engines. ;)
  • 347. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    Actually it was me that said that but our "friend" is
    not very good at quoting things so it's obvious
    someone else said it.
    I realised it was not Albert who said this. I was commenting on it out of merit, and not based on who said it. I didn't actually realise who DID say, but now I know, thanks.
    The point is that you may perform your restriction of
    the domain on the database itself, but it is Business
    logic and not something directly relating to data
    types on the database. Therefore, good design should
    ensure that database design and business logic are
    clearly distinct. Otherwise you'd spend all your
    time creating a whole set of datatypes to create your
    tables with and you'd see something like:
    I agree it is not always easy to restrict the domain of types in Oracle fully, due to the weakness of SQL in general, but also because Oracle types are so badly implement.

    In principle, the domain restriction belong in the database and not in the client application. Remember that a SQL DBMS is there to serve data and is not designed as the "back end" to one application. There may be many application use the same database, with concurrently or temporally separated. Good data modelling dictates all data attributes are modelled correctly.

    So what am I say this for? This is also an issue with is so wrong with Albert's implied meanings of empty string, since this is function of application and not of logic, or of database.
  • 348. Re: Treatment of zero-length strings as NULLs?
    521710 Newbie
    Currently Being Moderated
    I have very low IQ so I wanted to ask who is the most clever here?

    Albert, Billy, APC, Flying Spon, bleushadows, 3360, William, Kamal, ...., or maybe me - Dummy

    Please tell me I really want to know!!!

    THANKS!
  • 349. Re: Treatment of zero-length strings as NULLs?
    6363 Guru
    Currently Being Moderated
    Wouldn't your time be better spent finding a database
    you are capable of understanding and learning to use
    without whining and leave Oracle alone?
    The issue is not about being capable of understanding
    something. There is null to understand. It is about
    believing weather something is implemented the best
    way. You seem to like throwing that one at people.
    Why did you bold that bit.

    The important point was without whining.
    Wouldn't your time be better spent finding a database
    you are capable of understanding and learning to use
    without whining
    and leave Oracle alone?
    I've whined earlier about having to write a trigger
    everytime I need a surrogate key because Oracle does
    not support the identity column. That does not mean I
    don't understand triggers.
    I didn't say it did, I just said that its easy to do if you just focus on doing it instead of complaining about the way it is done. Is it that hard, no. In fact in the case of null vs. '' it is most often easier than constantly having to code for both cases.

    Guess what, a database, or application language is just a tool that can never keep everyone happy.

    Given this fact, there are two kinds of people. Those that just get on with it and those that whine and complain about it.
  • 350. Re: Treatment of zero-length strings as NULLs?
    BluShadow Guru Moderator
    Currently Being Moderated
    In principle, the domain restriction belong in the
    database and not in the client application. Remember
    that a SQL DBMS is there to serve data and is not
    designed as the "back end" to one application. There
    may be many application use the same database, with
    concurrently or temporally separated. Good data
    modelling dictates all data attributes are modelled
    correctly.
    Absolutely, I never said business logic should be applied in the client application. As far as I'm concerned the client application is just the user interface. In a 3-tier design you go for database level, business level and interface level. The first is purely the table structures, the second implements business rules/logic (and the data domains) but also should be implemented on the database and the latter is in whatever client interface tool is required.
    So what am I say this for? This is also an issue with
    is so wrong with Albert's implied meanings of empty
    string, since this is function of application and
    not of logic, or of database.
    And that's exactly the point I was trying to get him to answer on, but he never did. The user of an application can't differentiate between entering an empty string or a null value, so there's no need to be able to store both. If they don't enter anything in a field, they don't enter anything, so that field has an unknown value. If they want to flag that it is "known empty" then an addition field/checkbox is required and the value of that should be stored on the database alongside the null data.

    Glad we agree.

    :)
  • 351. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    > The user of an application can't differentiate between entering an empty string or a null value,

    I think the empty-stringist approach would be to let the end user enter a space character and have the application right-trim spaces from all character strings, and for this operation to result in an empty or zero-length string. It would seem pretty odd (and wrong) to me as an end user to enter a space to mean something other than a space but I suppose I would get used to it.

    Or I suppose the application could have an explicit "no value" checkbox next to certain fields, and translate this into an empty-string to send to the database.

    Of course then you might wonder why such a checkbox is so vital for character strings but not needed for other datatypes. None of the empty-stringists in this thread were able to answer that, and Albert thought it was an irrelevant and stupid question. In fact any attempt to explore his supposedly real-world application example were dismissed as trivial and missing the point, which suggests to me that Albert's real issue was a purely theoretical one.
  • 352. Re: Treatment of zero-length strings as NULLs?
    BluShadow Guru Moderator
    Currently Being Moderated
    Well I know that if I enter a space in a word document and save it, I'll expect the space to be there when I come back to it.

    ;)

    If the user flags something then the flag should be stored on the database and then the flag can be shown to the user when they retrieve the data, otherwise, as I showed in my Ingres example, you could look at the data and not actually be able to see what is null and what is empty.

    What would you rather see when querying the database...

    A combination of nulls and empty strings that look the same...
    ID  DATA
    === ===============
    1
    2   mydata
    3
    4   somemoredata
    5
    or all of the data with nulls but a flag to say what is known to be empty...
    ID  DATA            KNOWNEMPTY
    === =============== ==========
    1
    2   mydata
    3                   Y
    4   somemoredata
    5                   Y
  • 353. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    I imagine one objection to implementing explicit flags as separate database columns is that it could clutter the table design with a large number of extra columns, each having a logical relationship with its paired column that would require policing via constraints or triggers or both.

    Also you would have to remember to use this approach only for character columns ;)
  • 354. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    A common "correct" taught method of eliminating nulls is through the use of decomposition. So if an attribute can be missing it becomes a table. Multiple reasons for missing attribute become multiple table.

    Users, User_Phone_Numbers

    Users, User_Phone_Numbers, User_Has_No_Phone

    When the attributes are recomposed into a view the "one kind of null" for missing attributes values becomes closer to the relational null consequence of outer join operator. And evaluation of missing data becomes rich in meaning. Recomposition including certain tables has apparent meaning : users join user_has_no_phone for instance, or users join user_withhold_phone

    This way you never insert or update a NULL, but only select and meaning of selected NULL is well known, single meaning. So you have "pure 3VL"

    Controversy of this kind of design is considered that definition of entity becomes fractured into definition of facts, and debate of orthogonality.

    edited:

    And William Robertson can apply to all datatypes! not just varchar2

    Message was edited by:
    The Flying Spontinalli
  • 355. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    So in Albert's original example that he is not interested in, the end user would enter a space or perhaps click a checkbox to indicate a "we-know-there-isn't-one" value. What would be entered into the database would be a top-level row in CUSTOMERS, no row in CUSTOMER_PHONE_NUMBERS and one row in CUSTOMERS_WITHOUT_PHONES that included a reason code indicating "Customer does not have a phone".

    I can imagine it is easy enough for relational theorists to dream up this sort of design and simply demand that vendors magically solve the obvious practical problems it would entail.
  • 356. Re: Treatment of zero-length strings as NULLs?
    BluShadow Guru Moderator
    Currently Being Moderated
    It's definitely a feasible alternative, but boy oh boy, it sounds like a lot of hard work just to flag that something is known to not exist. I would imagine it would also have an impact on query performance.
  • 357. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    I believe the standard argument runs along the lines of "the vendor is free to use the physical independence principle to implement such systems in efficient manners"

    When you think of the wonders that are achieved by the Oracle optimiser, it's not a far leap of the mind to imagine that, should we all dedicate our minds and time to better use of relational concepts, rather than trying to for instance wedge XML in for some odd reasons, then such things could be achieved.
  • 358. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    I think this sort of feature would be easy to implement in such a CASE tools as Designer.

    Maybe it's a lot of work on implementation. But not hard work, it is easy to automate.

    It could be the benefits are far outway the negatives, especially if it means more thinking happens in the front of implementation, with less happening after deployment, when queries onto the flexible generic data structure finds out is too slow.
  • 359. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    So in Albert's original example that he is not
    interested in, the end user would enter a space or
    perhaps click a checkbox to indicate a
    "we-know-there-isn't-one" value. What would be
    entered into the database would be a top-level row in
    CUSTOMERS, no row in CUSTOMER_PHONE_NUMBERS and one
    row in CUSTOMERS_WITHOUT_PHONES that included a
    reason code indicating "Customer does not have a
    phone".
    In fact the GUI can (and should) look somehow like the user wants to see, but the database should be logically correct but also artful rendering of the data model.

    There is no need for a "reason code" in the database, since such a fact is already explicitly dictated by the predicate modelled "customer_without_phones"

    Also we see obeyed the closed world assumption here. If it is not in "customers" then the person is not such, if it is not in "customers_without_phones" then the customer has a phone. This is subtler, richer data resouce than merely: phone_number NULL.