Changing foreign key meaning. How to handle?
Lets say I have 2 tables:
- SpeciesId - foreign key
If you give the end-user ability to change SpeciesName, that means they can affect the species of all animals that reference the changed record (at least from user standpoint). This may be a bit of an extreme example, but how are situations like this usually handled? Put the responsibility on the end-user to know what they are doing? Disallow name change if it has been used before?
We are discussing this situation at work and I want to get input from some others. One of the solutions that was brought up was to remove the foreign key (e.g. put a text field for species in the Animal table). This doesn't seem right to me, because at what point do you draw the line of using foreign keys? To me it seems like more of a training issue to make sure admins understand the impact of the changes they make. I know it's an open-ended question and it may vary per scenario, but I'm just trying to get some general opinions.
This is a design decision that you have to make. You need to determine what is more important from a business perspective. Do you value historical accuracy or efficiently updating the information?
In your example, I would put less emphasis on history for the following reasons.
Only the most recent convention is significant. Assume an animal moves from one genus to another, it really doesn't provide any value to know what the old and now invalid genus was.
All animals of the same species should have the same species ID. You get this for free with Foreign Keys. Assume a tiger was added prior to a species name change. Then a different tiger was added after the species name change. Both tigers still belong to the same species.
Querying the database by ID will be easier and more reliable than using a string let alone delving into the dirty business of string parsing. You don't need to worry about character encoding, capitalization, white space, punctuation, etc. Assume that you would like to retrieve all animals of one or more species.
Put the responsibility on the end-user to know what they are doing?
You need to decide what the end user is able to update. If your end user is a biologist that is well informed about the scientific names of the species, he should be able to update this information. Otherwise, maybe it's a good idea to prevent the user from modifying this column at all, or only if this particular species has any animal associated with it.
One of the solutions that was brought up was to remove the foreign key
Don't do that. You will lose the ability to join the information from these tables. Imagine your table Species has a column "Continent", indicating if the species is found on America, Africa, Europe, Asia, etc. If you use the foreign key, you can ask questions like "What are all the animals that belong to an american species?" This will be impossible if you remove the foreign key.