Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2004-07-02 : 11:58:19
|
| Hi,Our company writes insurance rating software. With this, there are tons of factors that affect a company's insurance rate. These factors are all stored in different rating tables. We have a main "Companies" table which stores general info about these companies (the database holds data about a number of companies). Each set of data which affects rating is stored in a rating table, which has a foreign key associated with the main "Companies" table. Well, for very complex companies the number of rating tables can get quite large. With each one having a foreign key to the companies table, the companies table now has a large number foreign keys (~125-150) associated with it. How many foreign keys is too much? Will we start seeing performance issues when the main table has so many foreign keys? We can't really join to any other table since each set of rating data (indivdual rating table) is pretty much stand alone (when possible we combine tables but you wouldn't believe all the random stuff companies use to calculate the rates). Our Erwin diagrams look like a giant wheel with many spokes. Should I be concerned? The only other solution, would not to have foreign keys but I think that will open us up to data integrety issues (lose child records -- extra data which will never be referenced).Nic |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-07-02 : 12:17:58
|
| You could look at CONSTRAINTS as a way to ensure a domain of acceptable values is inserted in the fields. These would be good for Yes/No, Smoker/NonSmoker... types.Indexes, indexes, indexes... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-02 : 13:23:46
|
| If you DO throw away the FKs (are they really useful once the software is properly tested?) you could run "check reports" periodically to prove that no violations creep in.Or you could enfore them in the SProcs or Triggers instead.Dunno if that's a good idea or not though.Kristen |
|
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2004-07-02 : 14:10:48
|
| Once the db is in production, the FK aren't really needed. Companies are not removed from the database (except in very very rare cases). Is there much of a performance hit by having all of these FKs? If there is a fair amount of overhead with the FKs, I could drop them.Running a report looking for lost records could be sufficient. Do people typically have lots of FK, or only when necessary?Nic |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-02 : 14:25:28
|
| You know what....Unless it's 100% read only or close then lose the FK's...but unless I see performance degradation....I keep themBrett8-) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-02 : 18:39:16
|
| I can't believe anyone would tell you to lose the foreign keys. That's just irresponsible if the database isn't read only. Your job is to protect the integrity of the data. You need to look at your design and see how you can make it the most efficient, while maintaining this integrity. Foreign keys going out from a table is not a big deal, unless you want to delete out of that table. At that time, you can take a serious hit. If you DO throw away the FKs (are they really useful once the software is properly tested?) you could run "check reports" periodically to prove that no violations creep in.It's important because tested software still has bugs. Have you ever seen perfect software, because I haven't ran into any of that yet.Or you could enfore them in the SProcs or Triggers instead.That would be less efficient and more overhead.Once the db is in production, the FK aren't really needed. Companies are not removed from the database (except in very very rare cases). Is there much of a performance hit by having all of these FKs? If there is a fair amount of overhead with the FKs, I could drop them.All the more reason to keep the foreign keys. There isn't that much of a hit from having them; and you're protecting the data.Running a report looking for lost records could be sufficient. Then you'll get to restore a backup and try to piece together your data if it ever shows anything. If you're going to drop the keys, just blissfully drive off into the sunset and forget the reports.Do people typically have lots of FK, or only when necessary?Whenever they are needed, meaning whenever a relation exists.Now, having ranted, there are exceptions to everything. :) I just don't think this is one of them.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
too many foreign keys?