Servage Magazine

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:

CREATE VIEW SalePerOrder
   AS
  SELECT orderNumber,
  SUM  (quantityOrdered * priceEach) total
  FROM orderDetails
  GROUP by orderNumber
  ORDER BY total DESC

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.