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?
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.