0

I was wondering, keeping performance and optimizations in mind, while designing the database, is there any advisable number of foreign keys you should keep in a single table?

At present, few of my tables are using 4 foreign keys. Would like to know, if number of foreign keys in a single table causes some performance or scalability issues?

I am using MySql

closed as primarily opinion-based by RolandoMySQLDBA, Philᵀᴹ, Paul White, Mikael Eriksson, Max Vernon Jan 29 '15 at 18:39

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

0

Yes, ultimately FKs can affect performance. They will certainly affect insert/delete performance (more lookups), but you have the advantage of guaranteed data integrity.

You should be more than happy to pay a high price for data integrity - a high performance database with inconsistent data is worse than useless - certainly not as much use as a database that's a bit slow but with consistent data.

Yah gets what yah pays for!

  • Is there any recommended number of FK in a single table? After which generally it is advised to rework on table designs? – Abhi Jan 29 '15 at 5:13
  • 1
    No. Some reference tables will themselves have no FK constraints. This is almost a religious matter in many ways. My own personal belief is that as a rule of thumb, tables ideally shouldn't have more than 30 fields, and in a scenario like that, more than ~8FKs would seem excessive. Also, having many small tables means that a system can be more flexible. However, it can depend on your own system's requirements - I did once work on a system with > 35,000 (yes, 35 THOUSAND) fields but I wouldn't recommend that for sanity! – Vérace Jan 29 '15 at 5:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.