This was a tough question, if only to decide on what level to answer. The question makes sense on so many.
Generally speaking, a change in Mr. X's address information could update the address information that Mrs. Y has on him. It would depend on whether the database designer wanted to allow it or prevent it, and this might depend on whether Mr. X could come after you with lawyers if you release his personal information to Mrs. Y.
The privacy issue aside, a feature to "automatically update" is usually quite desirable, and thankfully very easy to accomplish. You do it by not allowing Mrs. Y to have a copy of Mr. X's address in the first place. Instead, the database designer implements a relationship between Mrs. Y and Mr. X's address -- a link. This is basic relational modelling.
The rule is: there is only one copy of Mr. X's address.
You asked about the cost. It costs more if you break that rule. Don't let anyone suggest a trigger, which is expensive to develop and not required until after the rule has been broken.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002