in

The ORM Foundation

Get the facts!

Disjunctive Reference Schemes

Last post 03-28-2009 18:55 by Terry Halpin. 20 replies.
Page 2 of 2 (21 items) < Previous 1 2
Sort Posts: Previous Next
  • 03-28-2009 3:29 In reply to

    • PeterC
    • Top 25 Contributor
      Male
    • Joined on 08-22-2008
    • Sydney, Australia
    • Posts 22

    Re: Disjunctive Reference Schemes

    Hi Andy,

    You don't need a second subset constraint because 'has first' is mandatory.

    I'm really more interested in your statement that Primary Key columns should never contain NULLs.  Why?  I'm happy to create tables with NULLs in PKs and FKs if it achieves what I need.  (Not that it happens often, mind.)  (I always guarantee my databases to at least 3NF/BCNF which I find adequate and practical for most cases.)  So I'd very much like to know what the issue is with them.

    thanks, Peter

  • 03-28-2009 4:52 In reply to

    • Andy Reiser
    • Top 50 Contributor
      Male
    • Joined on 01-24-2009
    • Sunshine Coast, Queensland, Australia
    • Posts 14

    Re: Disjunctive Reference Schemes

    Yes. You are correct. The subset constraint would be implied by virtue of that mandatory role constraint. My Bad.

    I guess my statement about PKs not containing NULLs stems from a lack of experience. I've never gone there. All I could see would be pain. I could not imagine an identifying attribute combination which contains unkown values.

    If you say that it is workable, then I am satisfied with that.

     

     

  • 03-28-2009 7:20 In reply to

    Re: Disjunctive Reference Schemes

    Hi Peter,

    PeterC:
    I'm really more interested in your statement that Primary Key columns should never contain NULLs.  Why?

    Here is the answer to your question:
    1. As Terry explains in his book, the term key means "a minimal combination of columns where no duplicates are allowed. (Halpin & Morgan 2008:47)
    The term "key" is from the principles of the relational model of data proposed by the late EF Codd in 1969. (Codd 1970)

    2: The term "primary key" is based on the notion that a relational table may have more than one "key". If so, each of these keys is called a "candidate key" because each candidate key offers an alternative means of uniquely identifying a row in the table.   If more than one candidate key exists then one of the candidate keys must be designated as the primary key.  (Halpin & Morgan 2008:476)

    So my first observation is that your statement "I'm happy to create tables with NULLs in PKs and FKs" seems to have a number of problems.
    For example, lets say that you have a table that uses a single column as a key.
    If you allow that column to have nulls (lets say there are three rows with nulls) then it is not possible to uniquely identify any of the three rows.
    By extension, this principle also applies to keys that span more than one column.

    Now - regarding your comments about "I always guarantee my databases to at least 3NF/BCNF"
    If you Google for definitions of 3NF you will find statements such as this for BCNF: "Each attribute must represent a fact about the key, the whole key, and nothing but the key"
    So, by definition, 3NF/BCNF require keys to be unique identifiers for rows.

    The idea of "having nulls in a unique identifier" seems a bit of a contradiction to me - but I'm always willing to learn.
    So I'm really curious - how can you "guarantee 3NF" if you allow what you are choosing to call a "key" to contain nulls?

    Ken

    References:

    Codd, E F, 1970, A Relational Model of Data for Large Shared Data Banks, Journal of the Association of Computing Machinery, Volume 13, Number 6, June 1970  http://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

     

    Halpin, T. & Morgan, T. 2008, Information Modeling and Relational Databases:Second Edition, Morgan Kaufmann, San Francisco  

    Filed under:
  • 03-28-2009 8:56 In reply to

    • PeterC
    • Top 25 Contributor
      Male
    • Joined on 08-22-2008
    • Sydney, Australia
    • Posts 22

    Re: Disjunctive Reference Schemes

    Thanks, Ken.  That was a good explanation.

    (After I hit the Post button I realised that I had stuffed up my wording - which means I really shouldn't Post when I'm tired.)  On the occasion I have had nulls in keys, they were always composite keys, and were mainly used to hold the results of queries with outer joins, rather than be the primary source of data.

    regards, Peter

  • 03-28-2009 9:30 In reply to

    Re: Disjunctive Reference Schemes

    Hi Peter,

    Thanks for the acknowledgement.
    Since we have gone this far I feel that I must respond to your last point as well.


    As I tried to explain in my earlier post, by definition, a key cannot contain nulls.
    So, whatever the collection of data that you are talking about, if it has nulls - then it is not a key.

    Hope this helps.
    Ken

    Filed under:
  • 03-28-2009 18:55 In reply to

    Re: Disjunctive Reference Schemes

    Real life is sometimes more complicated than one might wish. There are many examples in practice where disjunctive reference schemes are used (the botanical example in both editions of my book is a simplified version of one of these cases).

    The problem with Andy's initial example that gave rise to the error message seems to be that the external uniqueness constraint was not declared to be preferred (if it was, it would show with a double bar, which ORM2 uses instead of "P"). As discussed in my book, it is best to avoid disjunctive reference if at all possible, since disjunctive reference is awkward to implement. You can avoid disjunctive reference by introducing a simple artificial id, or by concatenating components, or by using special values instead of nulls (where possible). If you do that, you still need to enforce uniqueness over a combination of components some of which may be null. Andy is right in that the relational model requires no nulls in primary keys. However, practical relational DBMS’s allow you to declare tables without primary keys, and there are other ways of enforcing uniqueness constraints over column combinations with at least one nullable column.

     

Page 2 of 2 (21 items) < Previous 1 2
© 2008-2014 The ORM Foundation: A UK not-for-profit organisation -------------- Terms of Service