Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2004
    Posts
    12

    Question Unanswered: A question about db relationships

    I am starting a new project and somebody told me at work that if I create relationships (foreign keys) between my tables, it makes the database slower, the best way is to "link" the tables using sql instructions like inner/outer join.
    Is that true?

    Thanks in advance,
    venusgirl

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what moron told you that?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Many developers are leary of Foreign Key relationships becase it "takes away" some of the control they have in the app area. During the development phase of a new project, it can also be a pain to clear out data in a table that has foreign keys enabled.

    I am not aware of any (significant) performance penalty that foreign keys impose on a database. They are of tremendous value long term in maintaining relational integrity and for a host of other reasons.

    In the end, Brett's assessment is...er...rather more to the point, but spot on.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Oct 2004
    Posts
    12

    Thanks for the answers

    So I see that there's no performance problems in creating my database with foreign keys. Thanks guys.
    One more question, that could be applied to any database or only to SQL Server?

    Thanks,
    venusgirl

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is always some perofrmance cost associated with foreign keys, because the database needs to do a lookup to ensure that the parent row exists. That cost is normally trivial, and compared to the human cost of doing without foreign keys it is always trivial.

    Some databases impose a higher "performance price" than others for implementing foreign keys, but I've never seen one where that cost was material. Compared to the hours of maintenance time and the uncertainty of the users when a system runs without foreign key definitions, I see them as cheap.

    -PatP

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    "Data Sanitation" after the horse has left the barn is a very time consuming thing.

    I like the fact that I don't have to believe or rely on developers who say they will "take care of everything"

    They always lie

    DB2/Oracle/SQL Server and most database management systems take care of RI.

    I wonder about MySQL though... I really should play with that...which is what this board is based on I believe
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    All current (post 4.0) flavors of MySQL can declare RI (referential integrity). At least one of the flavors of MySQL (InnoDb) can actually enforce RI. Very few of the MySQL implementations that I've seen use a file engine that can actually enforce RI even though they can declare it, which makes me really uncomfortable.

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the point of declaring RI if it's not enforced?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    What's the point of declaring RI if it's not enforced?
    It makes lovely documentation?

    -PatP

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Really, this thread could have stopped with Brett's first post.


    But...MySql can't enforce relationial integrity? I had no idea. In my opinion a database without primary or foreign keys is not a database. It is a datapile.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    But...MySql can't enforce relationial integrity? I had no idea. In my opinion a database without primary or foreign keys is not a database. It is a datapile.
    Not exactly... MySQL actually supports mutliple (at least three) file formats for holding data. One of those formats (InnoDb) allows you to support DRI.

    The "Front End" for MySQL always supports DRI syntax (at least since version 4.0), whether or not the currently selected back end will enforce the DRI or not.

    I've never seen an ISP host InnoDB, but that doesn't mean that none of them host it. InnoDb is considerably more complex to manage, and resource intensive than MyISAM. Because of this choice that is commonly made by implementers in the name of speed and ease of maintenance, MySQL gets a bad name.

    -PatP

Posting Permissions

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