Right, I'm building a db for a beer festival using MySQL. The tables I ve got are as follows:

--brewery(bid, name, address, phone, region, notes)

--wholesaler(wid, name, address, phone)

--order(ordered, status, dateordered, datedelivered, method, sid)

--beer(caskid, name, ABV, size, category, notes, bar, position, bid, orderid, tripid)

--trip(tripid, destination, tripdate)

foreign keys: sid can be either bid or wid, bid links brewery to beer and tripid links beer to trip.

The problem I have is that I want to avoid using NULL foreign keys ()as they can cause weird joining problems, and the foreign keys orderid and trip in beer may be NULL - A beer may not be on an order and a beer may not be on a trip. The db books Im reading say that NULLS should be avoided "if at all possible"...

I decided to use intermedate tables to model these troublesome relationships (like many to many relationship tables) and will enforce constraints on updates so a beer can only be entered into these tables only once (to make the relationship 1 to many rather than many to many).

Can anyone suggest a solution or comment on mine?

Cheers in advance.

J

the only comment i have is that your book is doing you a disservice to suggest avoiding nulls "if at all possible"

nothing wrong with nulls

(of course, there are a couple of guys at http://dbdebunk.com who would argue, but let's not go there, okay?)

let's say you had a brewery for which you did not have an address

oops, can't use nulls, split address into its own table

let's say you had a brewery for which you did not have a phone

oops, can't use nulls, split phone into its own table

let's say you had a brewery for which you did not have notes

oops, can't use nulls, split notes into its own table

see where that's headed? you will need to join five tables just to list your breweries!!!

total crap, avoiding nulls

what would you use, empty strings instead?

feh, i say


rudy
http://r937.com/

Okay - in my Elmasri and Navathe book it says "As far as possible avoid placing attributes in a base relation whos values may frequently be null". It then goes on to say that nulls can affect inner and outer joins in different ways when used as foreign keys. I will not have any null values for almost all my attributes in the db apart from these 2 foreign keys. Do u think using an intermediate table (like with a many to many relationship) to link these two tables is excessive? I'd then have to have some sort of business rule to enforce the 1 to many relationship in these tables.

It seems strange to me that the two books Ive got have specifically warned against nulls especially those used in join operations. Im getting a bit confused...

i can't keep doing this on two forums

see http://forums.devshed.com/t104037/s.html


rudy

Perhaps they are refering to the behaviour of nulls in anti-joins etc., which can catch out the unwary:

SQL> select * from t;

ID VALUE
---------- ----------
1
2 2
3 3

SQL> select * from t where value != 2;

ID VALUE
---------- ----------
3 3

Note that the row with ID=1 is not selected by the second query.

This is something you have to watch out for, but I wouldn't go so far as to exclude nulls from my tables altogether.

And in the mark of true brilliance, Rudy rebuts his own post... :-P

Okay, let's sort out a few things. Yes, as Rudy points out, Date , Darwen and Pascal (at dbdebunk.com) argue that there are problems with nulls. However, DD&P also argue that the SQL language is fundamentally non-relational and broken and that the database products you're using are poorly designed and tie your logical design to arbitrary physical implementations. (And that this is all George Bush's fault.)

A good language would provide syntactic sugar to make it easy to split columns off a table. A C++ programmer doesn't call a method with (*obj).method, he uses obj->method because the designers of C++ realized that a common operation like a method call out to be as straightforward as possible. Likewise, it ought to be much easier to work with all those small fiddly tables a correct design demands, but the SQL standard just ignores that issue entirely, just as it ignores a raft of language design principles like, oh, orthogonality.

So in those cases where you have bits of information that are more or less accessories to the key, and where a blank value really doesn't have any logical meaning, fine, go with nulls. If you're going to join against something, it's probably better to make all the intermediate tables. I'd suggest writing some use cases to figure out what kind of joins you're going to be doing. That can also help you work out your predicates.

-- edit

Oh, also wanted to point out to rudy: most of the need for nulls can be avoided if you allow relation-valued attributes, including outer joins.