Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Table relationships in database

    We are having an internal debate regarding the table relationships in database. Can anyone tell me why we should use table relationship through database server rather than managing the data integrity through application (code)?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Because if someone else creates an application to load data into your database, you could end up with a lot of orphans.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by leezan
    Can anyone tell me why we should use table relationship through database server rather than managing the data integrity through application (code)?
    Because
    - your application will not be the only one to access the database
    - your application will not be the last one to access the database
    - a database with important data will live a lot longer than your application

  4. #4
    Join Date
    May 2009
    Posts
    3
    Thank you for the reply, so what will be the benefit of managing the data integrity through application (code)? if any?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by leezan
    Thank you for the reply, so what will be the benefit of managing the data integrity through application (code)? if any?
    The only advantage is to have an "early" error message to the user. You don't need to wait for the database to throw an error if the user entered invalid data.

  6. #6
    Join Date
    May 2009
    Posts
    3
    Thank your for your prompt reply. If I use the data relationship in the database, Is there any compatibility issue with other database engines like Oracle or MySQL when moving to these RDBMS?

  7. #7
    Join Date
    Mar 2009
    Posts
    349
    probably, but seasoned folks tend to keep their code as ANSI compliant as possible to avoid portability issues but of course the problem is none of the major vendors are ansi compliant.

    Outisde of relationships, constraints of all kinds are under used. If a business rule is always true, I tend to enforce it at the database level with constraints. That way it never gets broken.

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Some more benefits of enforcing referential integrity in the database:

    - the DBMS can generally do it more efficiently than your application
    - the query optimiser can take advantage of foreign keys in the database to improve query performance
    - users of the database will understand the data model better if they can see what constraints are enforced in it

    The basic foreign key syntax is the same in Oracle, MySQL and SQL Server.

Posting Permissions

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