• autoexcrement (4/18/2013)


    Thanks, I love your replies. 🙂 Few questions, if you have time...

    1) What do you mean by suggesting triggers on the view? I didn't know triggers could have anything to do with views...?

    Yes, triggers on views are allowed. For many views (but not all) they are the only way of allowing an update statement for the view. Look up "instead of" triggers in Books Online.

    Personally I'm biased towards using Stored Procedures rather than views to do encapsulation, even though I know views work very well in the simpler cases.

    2) If I end up with a bit field in the names table to indicate a "real" name, what is the best way to enforce a rule that each person has not less than nor greater than 1 "real" name flagged in the "names" table?

    The only way I know of doing it is with triggers. And it's a bit of a nightmare. So I don't think that is a good option - having the people table referencing the real name is much tidier.

    3) Considering #2, how to change a "real" name from one alias to another if the database doesn't allow a transitional state where any given person has either 0 or 2 "real" simultaneous names?

    You are asking the right questions! That particular question is why doing it with triggers is a thorough nightmare. Perhaps there are people who can tell you how to do it cleanly with triggers, but I'm not one of them. What you have to do is devise a sequence of statements each of which attacks two rows so that when you take the flag off one of them the same statement puts it on another. This is of course rather easier now that we have the MERGE statement in T-SQL, but it's still certainly non-trivial.

    Of course there are other questions you need to worry about in this area. Does it ever happen that two different people have the same real name? What happens if two different people have used the same alternative name? What happens when someone's real name changes? With luck you may be able to say you will not cope with some of the more irritating possibilities, but you need to look at these thigs early on to determine whether you need to include them in the design as not doing so you risk ending up with a nice design that you suddenly have to tear up because something you never thought about has now happened.

    Tom