When to use a View instead of a Table?

Oh there are many differences you will need to consider

Views for selection:

1. Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
2. Views can model complex joins easily.
3. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT function or many other things
4. You can easily manage your GRANTS directly on views, rather than the actual tables. It's easier to manage if you know a certain user may only access a view.
5. Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.

Views for insertion/updates:

1. You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view

Drawbacks

1. You lose information about relations (primary keys, foreign keys)
2. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you

Views can:

• Simplify a complex table structure
• Simplify your security model by allowing you to filter sensitive data and assign permissions in a simpler fashion
• Allow you to change the logic and behavior without changing the output structure (the output remains the same but the underlying SELECT could change significantly)
• Increase performance (Sql Server Indexed Views)
• Offer specific query optimization with the view that might be difficult to glean otherwise

And you should not design tables to match views. Your base model should concern itself with efficient storage and retrieval of the data. Views are partly a tool that mitigates the complexities that arise from an efficient, normalized model by allowing you to abstract that complexity.

Also, asking "what are the advantages of using a view over a table? " is not a great comparison. You can't go without tables, but you can do without views. They each exist for a very different reason. Tables are the concrete model and Views are an abstracted, well, View.

Views are acceptable when you need to ensure that complex logic is followed every time. For instance, we have a view that creates the raw data needed for all financial reporting. By having all reports use this view, everyone is working from the same data set, rather than one report using one set of joins and another forgetting to use one which gives different results.

Views are acceptable when you want to restrict users to a particular subset of data. For instance, if you do not delete records but only mark the current one as active and the older versions as inactive, you want a view to use to select only the active records. This prevents people from forgetting to put the where clause in the query and getting bad results.

Views can be used to ensure that users only have access to a set of records - for instance, a view of the tables for a particular client and no security rights on the tables can mean that the users for that client can only ever see the data for that client.

Views are very helpful when refactoring databases.

Views are not acceptable when you use views to call views which can result in horrible performance (at least in SQL Server). We almost lost a multimillion dollar client because someone chose to abstract the database that way and performance was horrendous and timeouts frequent. We had to pay for the fix too, not the client, as the performance issue was completely our fault. When views call views, they have to completely generate the underlying view. I have seen this where the view called a view which called a view and so many millions of records were generated in order to see the three the user ultimately needed. I remember one of these views took 8 minutes to do a simple count(*) of the records. Views calling views are an extremely poor idea.

Views are often a bad idea to use to update records as usually you can only update fields from the same table (again this is SQL Server, other databases may vary). If that's the case, it makes more sense to directly update the tables anyway so that you know which fields are available.