Tables and Views

I was involved in some analysis this week that really taught me the best practices in tables, views and access.

We had a new end user application that wanted to create a view to get access to a table of data (in this case all of the table).  In the process of figuring out the best way to do this Graham taught me about best practices and data performance.  The following picture shows how a best practice of table/view design should be done.  For us we do this on Teradata and it hums along.

Table_viewThe bottom layer is the physical table with the data (naming convention T_tablename).

For every table there is a base view created that mirrors the complete table (naming convention V_tablename).  This is done as no one is allowed to go against the base table.  Remember a view is not a copy of the data, but a window into it.

So now when you want to query the data you can go against the base view or build a new view that calls the base view and do what you want.

The idea is not to create a proliferation of views, but to assist in the control, performance and ease of integration.

Just some thoughts for a Saturday before the Grey Cup.

Leave a Reply

captcha *