Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Using MySQL views

Monday, May 28th, 2012 by Servage

Here’s a tip to all MySQL beginners or developers who settles for the basic selects and maybe using joins. Selects and joins are great and powerful, they enable you to do what you want. But there are cases where you want or need more. One more advanced requirement is the need for inheritable tables, or polymorphism. This is a more complex database model, but is used in many simple applications.

For example of you have a Content class as the parent for two content types, Posts and Articles. Now, in PHP you can easily extend those classes, but what do you do in the database? Sure, you could use a table for posts and one for articles, but then you don’t have unique content IDs. This is a problem if you want to make your content commentable through Comment class which stores in comments table. You need each comment to be associated with a content_id.

There are ways to solve the problem above, also single table inheritance is an option, where you combine the posts and articles in one table. But for now, lets see how you can emulate tables with MySQL views. This is practical for many scenarios.

Views are also good for saving queries you often perform. Take the following example:

  SELECT orderNumber,
  SUM  (quantityOrdered * priceEach) total
  FROM orderDetails
  GROUP by orderNumber

This demonstrates how you can have a MySQL view, which essentially is another query. You can now use the view instead of the complex query. Views can even include more complex queries to solve some of the datamodel problems described above.


Using MySQL views, 3.0 out of 5 based on 2 ratings
Categories: Tips & Tricks


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.