Link to home
Start Free TrialLog in
Avatar of Gina Bautista
Gina BautistaFlag for United States of America

asked on

Should I create SQL database table views?

I want to know if it's a good idea to have database views be used for queries and reporting instead of the live database tables?  This database has 100's of tables, but I know that for their reporting, they will probably only need 5 or 6.  Thoughts?  Any body have any recommendations, suggestions for creating database table views?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Views can add a layer of protection by masking the underlying physical structure of the database.  They can also hide columns developers shouldn't see.  They can also protect against database/table changes without having to recompile code.

They do add layers of complexity that may need to be maintained if you do change the underlying tables.

Depending on the actual query time from the base tables, you might also look into Materialized Views.
yes, try to have a Data Manipulation layer at your DB end to manage your data before interface it to other programming languages or systems.

for me, I would prefer to have stored procedures for most of the cases instead of views, tables, functions, etc but it's up to us on how to design such architecture.
Hi,

I would also recommend to always prohibit direct table access. An easy way is to use an own schema for all objects like views, SPs and functions which should be used in a specific frontend. On this way you can set permissions on the schema only and you can define different schemas for different frontends (i.e. the input frontend or the reporting frontend or Excel downloads etc.).

In case of a big database and many reportings you should also consider to create a second database as a data warehouse where the original data is transferred to and then use this for reporting purposes for better performance of the normal database and also the reporting. This can and should be (if possible) on a different server.

Cheers,

Christian
Avatar of Gina Bautista

ASKER

I do like the idea of having a second database as a warehouse, but wouldn't that entail having another SQL database license?  I have dabbled with views, haven't worked with stored procedures yet, but these are all very helpful suggestions and ideas.  Thank you.
Hi,

yes, in case that you install another instance on the same or other server it would need a new licence. If you create the data warehouse in the same instance it doesn't.

Cheers,

Christian
ASKER CERTIFIED SOLUTION
Avatar of Gina Bautista
Gina Bautista
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial