Re: Can we solve this -- NFNF and non-1NF at Loggerheads

lauri.pietarinen_at_atbusiness.com wrote:
> You are correct, except for perhaps the eliminating of NULLs (if
> you think that NULLs are a bad idea). Nulls would be represented
> by empty sets. Representing outer joins is more natural since rows
> with no corresponding "child rows" could be handled the same as the
> ones with child rows. Also, if you have a "multiway" outer join
> things get pretty tricky. Say if the person is associated with
> n emails and m phone numbers, you will get n x m rows with the
> person-columns repeated. These have to be eliminated in the
> application program.

Could you explain what exactly you mean by "multiway join"? - Google isn't being too helpful.

In your example, why can't you just have a table like this?:

(Tom,  comp1, a_at_b)
(Tom,  comp2, c_at_d)
(Dick, comp1, e_at_f)
(Dick, comp3, g_at_h)

I assume the idea is that different people can have different contact within the same company, companies can have several email contacts, and the same email address can be for someone working for more than one company?

If this last assumption isn't true (i.e if company is functionally dependent on email address), just chop out the company column and have a separate table keyed on email address that has a company attribute.

> Another benefit is lower network traffic (which can also be achieved
> via compression, of course), because the "outer table columns" are not
> repeated on each row.

OK, but wouldn't an optimization like this be best done at the physical level rather than the logical level? Premature optimization and all that.

> And further, it would enable a dynamic GUI generation, i.e. a GUI
> that is generated "on the fly"). And it would also be a "hint" to
> a report generator which can easily figure out the break point
> columns.

You mean if the network is so slow that the query result trickle over very slowly? Couldn't you get the same effect by just specifying an appropriate sort order to the query? I can't see that it would make much difference to a GUI whether the data came over in a nested relation or not.

Paul. Received on Mon Feb 07 2005 - 18:42:07 CET