This content has been marked as final. Show 385 replies
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.
Actually it was me that said that but our "friend" isI 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.
not very good at quoting things so it's obvious
someone else said it.
The point is that you may perform your restriction ofI 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.
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:
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.
Why did you bold that bit.
Wouldn't your time be better spent finding a databaseThe issue is not about being capable of understanding
you are capable of understanding and learning to use
without whining and leave Oracle alone?
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.
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 triggerI 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.
everytime I need a surrogate key because Oracle does
not support the identity column. That does not mean I
don't understand triggers.
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.
In principle, the domain restriction belong in theAbsolutely, 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.
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
So what am I say this for? This is also an issue withAnd 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.
is so wrong with Albert's implied meanings of empty
string, since this is function of application and
not of logic, or of database.
Glad we agree.
> 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.
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...
or all of the data with nulls but a flag to say what is known to be empty...
ID DATA === =============== 1 2 mydata 3 4 somemoredata 5
ID DATA KNOWNEMPTY === =============== ========== 1 2 mydata 3 Y 4 somemoredata 5 Y
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 ;)
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, 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.
And William Robertson can apply to all datatypes! not just varchar2
Message was edited by:
The Flying Spontinalli
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.
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.
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.
So in Albert's original example that he is notIn 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.
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
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.