1

I've been wondering if following approach is bad or really very bad:).

Question#1: can we use indicator_id to combine data from indicator_values and indicator_details tables (without using FOREIGN KEY) [we know we can, because we do this, but is it acceptable:)]?

Question#2: if we decide to use indicator_id (which in indicator_details table is not PK) would it have an important impact on performance?

Understanding SQL approach correctly, the best option would be to use iid (iid as FOREIGN KEY) in indicator_values table instead of indicator_id, but as always project has some limitation (like the way how users insert data - more below), thus we trying to find a compromise solution.

I aware of that this could cause data integrity issues, but solution without FK would be much user-friendly (since users don't have to worry about inserting into indicator_values table correct rows - rows with correct iid), especially that in our case data integrity is not crucial. Moreover if the only way (in our case) of inserting data by users is that is preceded by removing all rows from table, approach with FKs would cause deleting all rows (caused by ON DELETE ...) from indicator_values during indicator_details table update (again - update means: remove all rows then insert new data), so this would be time consuming.

DB:

indicator_values table

+----------+--------------+------+-------+
| vid (PK) | indicator_id | year | value |
+----------+--------------+------+-------+
|     1    | AACA         | 2001 |    10 |
|     2    | bbb          | 2001 |   100 |
|     3    | ccc          | 2001 |    10 |
|     4    | AACA         | 2002 |    20 |
|     5    | bbb          | 2002 |   200 |
|     6    | ccc          | 2002 |   300 |

indicator_details table

+----------+--------------+------------------+-----------------------------+
| iid (PK) | indicator_id |     desc         |          full_text          |
+----------+--------------+------------------+-----------------------------+
|  1       | AACA         | AACA is super    | Lorem ipsum dolor sit amet  |
|  2       | bbb          | bbb is extra     | Maecenas sagittis ultricies |
|  3       | ccc          | ccc is superb    | Praesent tincidunt nec orci |
1
  • FKs are to enforce referential integrity, not to allow joins per se. MyISAM had no FKs and still you were expected to join tables. Use EXPLAIN to see the query plan for your "non-primary" join. With the right index (depending on the actual query) there may even be no performance hit at all.
    – jkavalik
    Feb 5, 2016 at 12:45

2 Answers 2

5

There is absolutely nothing wrong with joining on columns that are not PKs/FKs. If you are concerned about efficiency then the key is to have appropriate indexes defined to support the join operations you are using. Also, don't assume that the existence of a foreign key implies the existence of an index - some databases automatically create such an index but many do not as it is not required and needlessly takes up space if it would never actually help with the queries you run.

Primary and foreign keys (and unique constrains and other database defined limits) exist for data integrity purposes.

Regarding FKs with ON DELETE CASCADE and people deleting parent rows: it is safer to no define ON DELETE CASCADE and let it error when people try rather than not defining FKs and other data integrity controls due to fear of human error because that is just saving yourself a nasty headache for later. Rows should not be updated by delete-then-reinsert, which is the behaviour that causes this concern, people should instead either perform a check-then-insert-or-update-as-needed or some form of UPSERT operation if the database supports on (MERGE in some like MS SQL Server, INSERT ... ON CONFLICT ... or similar in others, and so forth).

1
  • Yes, I completely agree that delete-then-reinsert approach is against keeping data in consistent state. Unfortunately, in this project DB is perceived only as a tool for simpler data manipulation which holds data coming from "trusted" source (I know, this is only assumption:) and nothing more. Users are not able to use any tool that allows direct access to DB, nor simple GUI for that purpose is planned, thus it would be difficult to follow "check-then-insert-or-update-as-needed" approach.
    – LAdas
    Feb 8, 2016 at 11:06
2

As mentioned in the comment by jkavalik constraints are there to enforce data integrity. While most modern optimizers can use the information in constraints to help make access decisions that is not their purpose.

Here is a question you need to ask yourself - if the integrity of the data is not important - then how important is the data? If it is worth storing then it is worth storing correctly otherwise your users are going to decide they can't rely on it to provide useful information.

Use stored SQL (ie stored procedure) to manage the data modifications and then the users will not have to worry about the correct order if that is a concern. I would also rethink the "remove it all and reinsert" pattern of data modifications. SQL does have an update that can handle thing quite nicely.

Just as a side note - and this is my opinion (although I have also seen it said by others) - I am not a big fan of FK's defined with the "ON DELETE CASCADE" option. Its one of those automagic things in a database that can have unintended side effects and can bite you later trying to track down an issue.

2
  • 1
    @jpmc - even if the subject is old it doesn't make it invalid. Even if there is an application on the front end I can guarantee you that will not be the only access to the data.
    – Kevin Walz
    Feb 6, 2016 at 1:51
  • Also - I wouldn't call constraints business rules as such - although the integrity can be defined by the business I would define them as data rules.
    – Kevin Walz
    Feb 6, 2016 at 1:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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