Creating a [materialised]view from generic data in Oracle/Mysql
- by Andrew White
I have a generic datamodel with 3 tables
CREATE TABLE Properties
(
propertyId int(11) NOT NULL AUTO_INCREMENT,
name varchar(80) NOT NULL
)
CREATE TABLE Customers
(
customerId int(11) NOT NULL AUTO_INCREMENT,
customerName varchar(80) NOT NULL
)
CREATE TABLE PropertyValues
(
propertyId int(11) NOT NULL,
customerId int(11) NOT NULL,
value varchar(80) NOT NULL
)
INSERT INTO Properties VALUES (1, 'Age');
INSERT INTO Properties VALUES (2, 'Weight');
INSERT INTO Customers VALUES (1, 'Bob');
INSERT INTO Customers VALUES (2, 'Tom');
INSERT INTO PropertyValues VALUES (1, 1, '34');
INSERT INTO PropertyValues VALUES (2, 1, '80KG');
INSERT INTO PropertyValues VALUES (1, 2, '24');
INSERT INTO PropertyValues VALUES (2, 2, '53KG');
What I would like to do is create a view that has as columns all the ROWS in Properties and has as rows the entries in Customers. The column values are populated from PropertyValues.
e.g.
customerId Age Weight
1 34 80KG
2 24 53KG
I'm thinking I need a stored procedure to do this and perhaps a materialised view (the entries in the table "Properties" change rarely).
Any tips?
