sql - Changing foreign key meaning. How to handle? -
lets have 2 tables:
species
- speciesid
- speciesname
animal
- animalid
- speciesid - foreign key
if give end-user ability change speciesname, means can affect species of animals reference changed record (at least user standpoint). may bit of extreme example, how situations handled? put responsibility on end-user know doing? disallow name change if has been used before?
we discussing situation @ work , want input others. 1 of solutions brought remove foreign key (e.g. put text field species in animal table). doesn't seem right me, because @ point draw line of using foreign keys? me seems more of training issue make sure admins understand impact of changes make. know it's open-ended question , may vary per scenario, i'm trying general opinions.
this design decision have make. need determine more important business perspective. value historical accuracy or efficiently updating information?
in example, put less emphasis on history following reasons.
only recent convention significant. assume animal moves 1 genus another, doesn't provide value know old , invalid genus was.
all animals of same species should have same species id. free foreign keys. assume tiger added prior species name change. different tiger added after species name change. both tigers still belong same species.
querying database id easier , more reliable using string let alone delving dirty business of string parsing. don't need worry character encoding, capitalization, white space, punctuation, etc. assume retrieve animals of 1 or more species.
Comments
Post a Comment