I'm working on an old web application my company uses to create surveys. I looked at the database schema through the mysql command prompt and thought the tables looked pretty solid. Though I'm not a DB guru I'm well versed in the theory behind it (having taken a few database design courses in my software engineering program).
That being said, I dumped the create statements into an SQL file and imported them in MySQL Workbench and saw that they make no use of any "actual" foreign keys. They'll store another table's primary key like you would with a FK but they don't declare it as one.
So seeing how their DB is designed the way I would through what I know (minus the FK issue) I'm left wondering that maybe there's a reason behind it. Is this a case of lazy programming or could you get some performance gains by doing all the error check programmatically?
In case you'd like an example they basically have Surveys and a survey has a series of Questions. A question is part of a survey so it holds it's PK in a column. That's pretty much it but they use it everywhere.
I'd appreciate any insight :) (I understand that this question might not have a right/wrong answer but I'm looking more for some information on why they would do this as this system has been pretty solid ever since we started using it so I'm led to believe that these guys knew what they were doing)
Having untrusted or disabled FOREIGN KEY or CHECK constraints in your databases will degrade data consistency and integrity and can cause query performance problems.
Foreign keys are just constrains which helps you to make relationships and be sure that you have correct information in your database. You should use them to prevent incorrect data entry from whatsoever. Save this answer.
The main disadvantage is the increase in size for the fact table records. You don't specify the sizes of the rows, but adding new foreign keys could significantly impact the size of the row. In most databases, increased row size in the fact table would slow down queries.
A foreign key might point to data that no longer exists, or the foreign key's data type doesn't match the primary key data type, eroding referential integrity. Referential integrity can also be corrupted if the foreign key doesn't reference all the data from the primary key.
The original developers might have opted to use MyISAM or any other storage engine that does not support foreign key constraints.
MySQL only supports the defining of actual foreign key relationships on InnoDB tables, maybe yours are MyISAM, or something else?
More important is that the proper columns have indices defined on them (so the ones holding the PK of another table should be indexed). This is also possible in MyISAM.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With