up vote 3 down vote favorite
share [g+] share [fb]

I'm part of a project that inherited a piece of software that has a unique MS SQL database design that I'm trying to understand why they designed it the way they did.

Basically for every table, for example table tab_user, there is a duplicate table that is called tab_user_view. The tab_user_view is populated with triggers placed on the tab_user table to populate the tab_user_view. All of our pages that list data retrieve data from the _view tables. I kind of understand this is to avoid locking and increase performance, but why wouldn't they just use traditional views? The original software was written in 2003 so is it possible some tech wasn't available that we now have? Thanks

UPDATE

-Yes, there is some data manipulation for the _view tables, but the same thing can be achieved with views, so why?

link|improve this question
4  
Sounds a terrible idea. If all of your queries are selecting from these _view tables then there is clearly no difference to all your queries just selecting from the base tables. Just additional overhead and inefficiencies and possible bugs of keeping the two in synch. – Martin Smith Feb 22 at 19:15
1  
It sounds like the architects just really didn't know what they were doing. Does it just do a straight copy from the tab_user table? Is tab_user where your data is inserted to initially from outside the database? – JNK Feb 22 at 19:16
It could be an attempt to prevent users from directly updating the tables by only granting them access to views, then managing all updates through triggers. Like you, it seems odd to me too. – user472668 Feb 22 at 19:18
1  
These views aren't even actual views. They are just tables named with a view suffix. – Martin Smith Feb 22 at 19:19
1  
Unfortunately unless the original designers are around to answer, you're probably never really going to understand why. There may be some clue in the trigger or in differences in the schema between the _view and non-_view tables, but I'm guessing it's more of a "let's create copies of this so readers don't block writers" misconception or some other "we don't really know what we're doing, but we can bill some extra hours if we add this stuff." – Aaron Bertrand Feb 22 at 19:27
show 4 more comments
feedback

migrated from stackoverflow.com Feb 22 at 22:27

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 5 down vote

The reason is because the skills for evaluating competence in any discipline are the same skills for competence itself. They thought this design was a good idea because they weren't very skilled in database design, and thus were not skilled enough to recognize that it was a terrible idea.

link|improve this answer
+1 - couldn't have put it better myself. – ConcernedOfTunbridgeWells Feb 23 at 12:42
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.