Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    NULLS - foreign keys

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  3. #3
    Join Date
    Dec 2003
    Posts
    2
    Originally posted by r937
    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

    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...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can't keep doing this on two forums

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


    rudy

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Originally posted by r937
    what would you use, empty strings instead?
    And in the mark of true brilliance, Rudy rebuts his own post... :-P

    Originally posted by braveheart
    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...
    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.
    Last edited by sco08y; 12-15-03 at 04:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •