We were recently disussing adding the ship-via method to the orders table because we had simplistic software mapping into our (or more precisely out of) our osCommerce database – it merely maps field for field.
So when you want to query something even mildly complex you have trouble – for example, the temptation to duplicate the ship-via field in the orders table.
That’s where VIEWs come in. A view looks like a table, but that’s about it. Under the hood, it’s really a SELECT statement that you access as if it were a table. The advantage being, for complex queries, a greatly simplified call.
Here’s a view I created in one of my osCommerce shops:
mysql> CREATE VIEW shippingView AS
SELECT orders.orders_id, orders.billto_lname, orders_total.title, orders_total.value
LEFT JOIN orders_total ON orders.orders_id = orders_total.orders_id
WHERE orders_total.class = ‘ot_shipping’;
Now, let’s see what shippingView
will look like to the outside world:
mysql> DESCRIBE shippingView;
| Field | Type | Null | Key | Default | Extra |
| orders_id | int(11) | NO | | 0 | |
| billto_lname | varchar(64) | NO | | | |
| title | varchar(255) | YES | | | |
| value | decimal(15,4) | YES | | 0.0000 | |
Now, if I were to run the SQL SELECT I used in the ‘CREATE VIEW’, I would get everything, right? Right, I would. But check out what I can do with the VIEW shippingView;
mysql> SELECT * FROM shippingView WHERE orders_id = ‘300’;
| orders_id | billto_lname | title | value |
| 300 | Jadhav | UPS (UPS Ground( <acronym| 33.8200 |
1 row in set (0.01 sec)
Hope this helps before some poor soul duplicates a field in their database!