Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    9

    Unanswered: Database Relationships

    Hi,

    Im managing our companys database which has approx 250 tables in it.
    It is a design i have inherited from the people who originally created it, and i'm just having a look at all the foreign key relationships in it.

    The actually have not defined many of these relationships. I'm just working through it now and placing these in now.

    My question is this: Being such a complex database, can you have too many relationship?
    For example, columns 'product_ID','Customer_ID', 'Branch_ID', and 'User_ID' occur in around 50 tables each.

    If I enforce all these relationships, is it going to have a negative impact on the database? Is there any other factors i should consider? Is there a limit to how many relationships you should enforce?

    Any help would be appreciated.

    Josh Dolan

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    IMO it would really depend on who is access the database and how....

    Sometime enforcing foreign key contraints is going to cause more problems then they are going to solve... but it should be evaluated on a case per case basis....

  3. #3
    Join Date
    Jul 2002
    Posts
    9
    I don't have any problem with enforcing all the constraints i want to implement. I would rather deal with the problems that it may occur and have them implemented than nothing at all.

    I'm just thinking from the performance and database design angle.
    Is it going to degrade performance that much? And, it is good database design to enforce most if not all foreign keys?

    Thanks,

    Josh

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Providing the foriegn key contraints are valid then IMO you can do no harm in implementing them providing you are happy to deal with possible issues.

    Performace may be affected but not to any real extent IMO.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't have too many relationships. They are necessary to maintain the relational integrity of your data, and if you remove them and allow inconsistencies to develop in your data then the extra coding and admin you will need to do to account for the errors WILL slow down your system.

    It is more likely that you have too many tables, not too many relationships. Unfortunately, if there is already an interface and/or reporting structure developed around this schema, you are probably stuck with it.

    The next time your company is considering developing an important database, advise them to hire a professional database designer to help them. A database is as important to an application as a foundation is important to a house. When I had my home built, I didn't go buy a book at Home Depot and then start pouring concrete myself. I hired professionals.

    blindman

Posting Permissions

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