Roy Hann wrote:
Twat.

If you want to avoid NULLs, you add tables. Cleaner, but typically slower.
Perhaps not the way it should be, but the way existing RDBMS' are.

On Nov 12, 12:25*am, Gene Wirchenko <ge... (AT) ocis (DOT) net> wrote:
from the very start. Right now, I am thinking about logical level,
i.e. the set of relvars an application will see and work with. On a
level of SQL planning I will use wide tables with NULLs in them, no
doubts.

On Nov 12, 4:33*am, Clifford Heath <n... (AT) spam (DOT) please.net> wrote:
add views which are projections of a NULL-containing table and would
work with them only, not with the table itself. Well, it would require
some self-control to not touch the table but only the views derived
from it.

On 10 nov, 10:18, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
jumbo.

Regards...
> Roy

On 12/11/2010 3:37 AM, Cimode wrote:
I must say I have heard people say exactly what Clifford wrote and I
seem to remember some app or other that separated most users from the
dbms so that they couldn't access base tables which were 'outer joins'
directly, so the application programmers never actually manipulated any
nulls. I saw that as a hack that reduced the dbms to an access method
but whether misguided or not, I'd say it's true enough that some people
think this way, putting the performance cart before the functional cart.
Personally, if there were no dbms that could handle a performance
requirement I would consider programming the app with an access method.
I think such apps are rare, perhaps found only in real-time automation
systems.


Roy H snipped the first sentence in Clifford H's paragraph: "You
shouldn't need NULLs in your conceptual model." I don't see any reason
to plonk that, nor the rest which could have been valid sarcasm. For me
the big problem with nulls is that I don't know how to express them in a
predicate. (Until the late 1990's, I never thought much about
predicates only because I hadn't seen much written about them. Instead,
I had thought of relations as being abstractions of what I called
uniform sentences. This 'thinking' was casual and a little fuzzy, not
formal at all but it did at least prevent me from associating two 'very
unlike' sentences with the same 'table'.)

On 12/11/2010 5:48 AM, paul c wrote:
Oops, meant performance fart before the functional horse or suchlike.

On 11/11/2010 11:32 AM, Hugo Kornelis wrote:
Maybe it's simple but I'm having a hard time understanding the above.
Is it even possible to reduce the number of predicates an app needs
without changing requirements (at least without de-normalizing)?


(By the way, I also don't see what's redundant in those 'Wario'
propositions above.)

On Thu, 11 Nov 2010 12:29:08 -0800 (PST), Kentauros
<joker.vd (AT) gmail (DOT) com> wrote:

Hi Kentauros,

I see no redundancy. These are five distinct facts. You can't remove
one of these facts without losing information.

Don't forget that in the context of databases, "redundancy" refers to
representing the same *fact* more than once, not about storing the
same *value* more than once.

However, there are alternative ways to represent the same information.
Some of them may use less facts than the five I used above; others may
use more facts. You should not mistake a difference in number of facts
stored for redundancy.

To clarify, let me forst extend my original example a bit, to two
characters and two characterstics;

"Wario has defense 17 in Super Mario World"
"Wario has defense 17 in Super Mario World 2"
"Wario has defense 17 in Super Mario World 3D"
"Wario has defense 17 in Super Mario World - the last part"
"Wario has defense 17 in Super Mario World - the sequel to the last
part"

"Peach has jump 5 in Super Mario World"
"Peach has jump 7 in Super Mario World 2"
"Peach has jump 7 in Super Mario World 3D"
"Peach has jump 7 in Super Mario World - the sequel to the last part"

As you see, the jump power of Peach is not the same in all games, and
in one game she has none.

These nine facts can also be represented in another way, if we also
represent facts about the order of these games. The alternative would
be to use these 8 facts:

"Super Mario World 2 is the sequel to Super Mario World".
"Super Mario World 3D is the sequel to Super Mario World 2".
"Super Mario World - the last part is the sequel to Super Mario World
3D".
"Super Mario World - the sequel to the last part is the sequel to
Super Mario World - the last part".

"Wario has defense 17 from Super Mario World until Super Mario World -
the sequel to the last part".

"Peach has jump 5 in Super Mario World".
"Peach has jump 7 from Super Mario World 2 until Super Mario World
3D".
"Peach has jump 7 in Super Mario World - the sequel to the last part"

Is this alternative better? Well, that depends. If you want to
minimize the number of facts, it is. If you want to minimize the
number of predicates, it isn't. If you want to minimize the amount of
bytes required to store the facts - well, I guess that depends on lots
of information I don't have. And if you want to minimize the
complexity of your queries, then, again, it depends on what kind of
queries you expect.

So which one you choose (and not just from these two; there must be
more alternatives) is up to you. As long as you stick to your choice.
You should not try to combine these alternatives - as that WOULD
introduce the redundancy you already thought to see in the first
alternative.


Ah, yes. That makes sense than.


Unless it is possible for a character to exist in a game with no
characteristic at all (and you have already told us it isn't), the
existence of a character in a game is implied by the character having
characteristics in that game; adding a seperate relvar to store its
existence would be redundant.


(snip)
Most people in the field will tell you that it is not good practice to
have constraints that need to change based on foreseeable changes in
the world.
They'll also tell you that is is best not to mix data and metadata.

Your constraint violates both advices.


(snip)
Yes, that is true. Welcome rock, meet hard place. You'll either have a
model that's easy to query and has lots of nasty constraints, or you
can choose a model with relatively simple constraint that is tough to
query. Neither of these choices is automatically better than the
other; it's a choice you'll have to make, based on how you expect the
system to be used.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

paul c wrote:
Exactly. A logical formula uses predicates over one or more "individuals",
and has no meaning when one of the individuals may be absent.

That's because you haven't followed fact-oriented modeling, which has
been built on these ideas since its inception in the 1970s.

FCO-IM and NIAM are also built on such sentences. They (and ORM2)
show how to break down such sentences into elementary form, which
is where only one fact is conveyed - nothing further can be taken
away without losing information. When facts are expressed in elementary
form, there is never a need for NULLs. The result has a direct mapping
to formal logic (as Halpin's thesis proved) and thus (like RM) forms a
formal basis for designing information systems.

Fact orientation has significant communication advantages when you're
engaged in requirements capture, because of its natural-language
foundations. The tools produce properly normalized relational models
automatically.

Clifford Heath, http://dataconstellation.com

On Nov 13, 2:49*am, Hugo Kornelis
<h... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

Oh. That makes sense. Thank you!

Ooops. Indeed. Thanks again.

Well... I don't think I agree, but maybe it is just because I have to
little experience in this field. I'll think about this.

I want to get, so if I get (after projecting JOINS) an empty relation,
well, that just means I requested for non-existing data on this
character in this game. Or that the character itself doesn't exist...
either way, it is easy to notice and easy to find out why it
happened :-)
And again, I can use views to group data by games.