in reply to OT: benefits of database normalization

I work with design one day to day, and I'll tell you why it's not ideal. If it lacks a single column primary key, some databases, like oracle, make it harder to do an outter join. The way to get around the oracle error is.. 

select
 *
from (
  select
    a.pk1
  from
     table a,b
  where 
     a.pk1 = b.pk1 (+)
) a_outter_joined,b
a_outter_joined.pk2 = b.pk2 (+) and
(a_outter_joined.pk1 = b.pk1 or a_outter_joined.pk1 = null )

[download]
You think you'd be able to do.. 
select
   *
from
   a,b
where
   a.pk1 = b.pk1 (+) and
   a.pk2 = b.pk2 (+)

[download]
Maybe I've assumed that there's a missing single key column that was just left out to make an illustration, but I rather not assume... Oracle 8 generates this error for the above query.

Update: Updated which version of oracle gens the error and which query does what.

----
Then B.I. said, "Hov' remind yourself nobody built like you, you designed yourself"

Replies are listed 'Best First'.
Re^2: OT: benefits of database normalization
by pg (Canon) on Oct 03, 2004 at 15:11 UTC
    "If it lacks a single column primary key, some databases, like oracle, make it harder to do an outter join."

    I am with dragonchild on this, and your assertion is not true. I deal with Oracle everyday, and never heard of this, as a matter of fact, most of our tables don't have single column primary key.

    Go even deeper, personaly I am against the idea of using artificial numbers as keys. If the primary key takes 3 or 4 columns, just take. In this situation, some people will make up a sequence number as single column primary key, I don't.

    Something a bit off topic, I hate using current time as part of the primary key. In this system I am supporting now, there are several tables are like this, and every time when two users try to insert to the same table at same time, one of them get kicked because of unique constraint. That's ugly, and is a design defect.

[reply]
Re^2: OT: benefits of database normalization
by dragonchild (Archbishop) on Oct 03, 2004 at 14:54 UTC
    If it lacks a single column primary key, some databases, like oracle, make it harder to do an outter join.

    Could you back up this assertion? I've never heard this and I work with an Oracle9i database that has no primary keys whatsoever. (Long story ... I have no control over it.) If this were true, I and the DBA could take it to our VP as reason #2348769 as to why the application developers shouldn't have control over the schema.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]
      The oracle error is "ORA-01417: a table may be outer joined to at most one other table."

      Someone else has the problem here. We use 9i at work as well, and I'll double check version numbers to get you something more specific in terms of version numbers in 24 hours. But last I checked, when I tried it, it barfed that error at me.

      ----
      Then B.I. said, "Hov' remind yourself nobody built like you, you designed yourself"

[reply]

        Your first query is fine, as b is out joined to two tables a and a_outter_joined, but your second query is not, as it twice out joined to a. Well oracle can make the error message more clear, saying that "you either out joined two tables, or out joined the same table twice." ;-)

[reply]
        That problem has nothing to do with primary keys, foreign keys, or anything of the sort. That error has to do with how modern RDBMSes handle outer joins. Basically, it goes like this:
        1. You construct your query. 
           from A
 left outer join B on (a.foo = b.foo)
 left outer join C on (b.bar = c.bar)

          [download]
        2. The query analyzer now tries to determine what order to join the tables to form the intermediate set that it will then limit on. (It's more complicated than that, but that's the basic idea.)
        3. Let's say it tries to join B and C first. It does that, forming a set with all the items from B and items from C that match. Then it joins that to A. But, what items should be in C for those items in A that aren't in B? You run into the same problem if you join A to B first, then join that to C.

        I guarantee that if you do this with all your tables having primary keys, that won't make a difference. If all your tables have foreign keys, then you're sidestepping the issue because the OUTER keyword won't mean anything.

        Being right, does not endow the right to be rude; politeness costs nothing.
        Being unknowing, is not the same as being stupid.
        Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
        Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

[reply]
[d/l]

In Section Seekers of Perl Wisdom