Lets say I have 2 tables:
Species
Animal
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.