Let's says I have a table which has many fields linked to values from other "value tables". Naturally, I declare foreign key constraints on each and evary of them to enforce integrity.
What if I finally get the number of such fields in the range of 20-30? Will it somehow "slow" table operations or not really?
ADDED: Value tables are expected to have only few records, normally 5-10 or something. The database is SQL Server 2008.
Having one foreign key slows down insert / update operations versus having zero foreign keys - just because database has to check that foreign key value actually exists. Having 30 foreign keys will be slower then having none.
That said, just how much slower it'll be depends on many things, including size of your value tables / database engine you're using / indexes / etc... and may be virtually negligible under best case scenario.
Yes, there is some performance penalty on inserts and updates as all the relevant constraints are checked, but it is unlikely to cause any issues unless you are trying to insert data at a high rate. It is usually more important for data to be maintained correctly rather than quickly, so the penalty is well worth incurring.
If you perform an UPDATE of a few columns, only the constraints on those columns need to be checked, and most DBMSs will only check those constraints.
SELECT statements will not be slowed down at all of course, and in some (probably rare) cases could even benefit from the optimizer being aware of the foreign key relationship between 2 tables that are being joined.
When you insert a row into your child table, the DB engine will look up if the corresponding values in the parent tables exist - that will use up some CPU and some logical reads. If your parent tables are small, they will most likely be in the cache, so you would not expect many slow physical reads as soon as your cane is warm.
What would concern me more is if you are ever going to delete from your parent tables: if you don;t have a corresponding index on your child table, the whole child table will be locked and scanned. On the other hand, if you do have corresponding indexes for all your foreign keys, then you may end up with up to 20-30 additional indexes on your child table, which is a considerable slowdown.
You might want to run your own benchmarks and see for yourself.
Of the 20-30 fields, how many rarely get used? Maybe some other table could be built. Makes it tougher to have to update two tables from a coding perspective, but would speed things up if you can omit updating the second table most of the time.
I deal with a 3rd party app that has main tables with corresponding 'custom' tables where we can setup our own fields. Unfortunately, we use the 'custom' fields all the time and rarely can get away with just dealing with the main table.
I think it's going to depend on if your queries use any of the contraints. If your query is going to need to check another table because of a constraint, then you'll see a performance hit. If your query doesn't reference any columns in the contraints, the performance hit will probably be negligible.
Like most everything database design related, this 'depends.' If your application does heavy inserting, updating and deleting you will run into performance issues. This could be a case where de-normalizing could be justified, especially if the 'value' tables are not changing.