Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    question on one-to-one Relationships

    ok, my current understanding is that the only time 1:1 is permissable, given Normalization, is when you have performance concerns in mind. Either:

    (1) You split one table into two in order to make table size more manageable, or

    (2) You have a field that is sporadically filled. in order to not waste SPACE declaring a variable you'll not use, and the TIME it takes to search a field when you really (should) already know its NULL, you declare a child table using 1:1



    My questions:

    - Is this understanding correct?

    - what is the terminology for these situations?

    - I'm working with a case of (2) above. So, i guess it becomes a question of how much waste you're willing to tolerate? Are there general guidelines for when to bother to declare a separate child table? I know that the answer will depend on % of records with NULL values in that field, and the data type of that field, but I mean specific rules of thumb or something.

    Thanks kids!!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: question on one-to-one Relationships

    Those are certainly reasons why it is sometimes done. I think any rules of thumb would have to be specific to the DBMS you are using. For example, Oracle wastes no space storing nulls in a column provided there are no non-null column values after it in the row. It is therefore good practice in Oracle to put all the NOT NULL columns before all the NULL columns. Oracle's guru Tom Kyte recommends against splitting tables on such performance/efficiency grounds unless they have 100s of columns:

    http://asktom.oracle.com/pls/ask/f?p...:9103542139079

    http://asktom.oracle.com/pls/ask/f?p...D:469621337269

    For other DBMSs, different criteria may apply.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another advantage of splitting a table into two, particularly if the second table split off contains a large amount of data, is that searches and scans on the first table should be faster, because there will be more rows per physical page on the drive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2003
    Location
    Australia
    Posts
    59
    pokeynyc,

    Your'e understanding is incorrect. Normalisation and performance have nothing in common. Normalisation is logical, performance is physical.

    A one-to-one constraint, AFAIK, is impossible to declare declaratively in DB2, Oracle and SQL Server. But I suspect you mean One-to 0 or One.

    Follow you functional dependencies to determine if this type of constraint is needed.

Posting Permissions

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