Re: SQL and defining data structures

In the meantime, as I mentioned to Birgitta I still won't create a VIEW if it's only used in one program, and in that case using externally described fields to build the data structure is perfectly fine.

We can discuss about this statement, but finally everybody has to decide by himself what may be the best for him.

This is actually one of my favorite discussions: the practicality of theoretical architectural decisions.

Those statement "I only use it in a single program" may be true today, and may be true tomorrow, but in 6 month someone decides that the result has to be downloaded with Excel or must be displayed in a web application or the java programmer has to consume the same data.
What unfortunately happens mostly, the complete SQL statement is copied (it's only only another program). ... and 1 year later the data is needed in another environment (only another program!)

An now imagine, you have to modify something, you have to find all occurrences of your SQL statement and modify it in all environments.
If you had a view, you'd simply have to modify the view.

I never state "I only need it in a single program", but "I expect it may be used in multiple programs". In this way I decide to create and use views.

But what if it truly is only used in that program?  Now I have two places where I have to make changes.  If I want to add a new field, I have to change the view and I have to change the program, whereas with a data structure and no view I can do it all within a single program.

I absolutely agree that when you're designing a system from the ground up you should have a consistent access.  This probably involves completely normalized tables, generated primary keys and a complete logical access layer.  Any exceptions to those should be made consciously and with good reason.

But when you're maintaining an existing system, it's sometimes the case that practicality and maintainability weighs towards a less "pure" solution.  For me, if I'm creating a new view that is used in only one program, I will create the SELECT statement in the program and create a data structure in the program based on templates of the tables in the SELECT.  I have one object to maintain and no synchronization issues.

If I later find that the view is relevant to another process, I can extract it from the program into a view, change the existing program to use the view, and I'm done.  But until then, I dislike having an extra object (the view) just to satisfy a possible future goal.

But that's just me.