Why are you people forgetting about VIEWS?
I don’t know why the fullasagoog list has turned into the SQL newsgroup lately but there are some good articles coming our way nonetheless.
The one thing that is bothering me though is that every single person out there is forgetting about views and their importance in database design.
Views are basically a query that act like a table. That’s the best part about them. Using views you can do insert, update, delete statements against them, include them in other views, trigger and stored procedures. Some database even allow you to have indexes against (MSSQL 2000 and up).
So why use them? Take this post about bad column names. Why subject yourself to remembering to enclose a column name with . Why not just create a view for the table and name the column what you want. For instance, take this crappy query:
You usually see this when someone upgrade a MSAccess database to MSSQL. Instead of trying to remember the column names and remembering to enclose the column name that have spaces in them within , just write a view for the table like so:
CREATE VIEW vwUsers
[pk_User] AS userid,
[user name] AS username,
[password] AS password,
[first name] AS firstname,
[last name] AS lastname
[active user] AS active
Now when you need to get a record from the user table, you can use the view and have the column name already to you liking. Plus you can perform all insert, update, and delete statements using the view instead of the underline table:
INSERT INTO vwUsers
Another cool thing is that you can use views to filter other views, so that you have to write less SQL code for you application. Take for instance the table above, let’s say it had 10,000 users in it and 5,000 of those users are inactive. You want to retrive all of the active users from this view. You could write another view to do this for you
CREATE VIEW vwUsersActive
WHERE active = 1
Now inside you CF application all you have to do is write a CFQUERY against the view to retrieve all the active user:
<cfquery name=”q” datasource=”#mydsn#”>
SELECT * FROM vwUsersActive
Obviously this is a very basic query and not al that pratical. However normally when writing an application you can get pretty complex with your queries and views are there to simply them.