BobWhitefield
Programmer
Say I have these four tables:
Product
productKey
productTypeKey
quantityOnHand
ProductType
productTypeKey
name
ProductAttribute
productAttributeKey
name
valueType -- 1=boolean, 2= float, etc.
productTypeKey
ProductAttributeValue
valueBoolean NUMBER(1)
valueFloat FLOAT
valueInteger NUMBER
valueString VARCHAR2(4000)
productAttributeKey
productKey
A Product represents a quantity of identical items of a specific ProductType (navy men's slacks, 32" waist, 34" inseam uncuffed, for example). For each ProductType, the ProductAttribute table defines a set of attributes supplied at runtime by users of the application. For example, they might create a ProductType named 'PantsCo Slacks' that has ProductAttributes named 'inseam' (float), waist (float), 'color' (string) and 'cuffed' (boolean). The values of those attributes for a particular Product (32.5, 34, 'navy', 0) are then stored in the ProductAttributeValue table, one row for each value.
(By the way, this is a toy example--the real problem could have dozens of attributes for each product type, and thousands of product types, hence the need for this kind of approach.)
Managing this indirection is straightforward in a programming language, but I also need to make things easy for people using SQL directly. So here's my question (finally!): is it possible to create a VIEW (using dynamic SQL) with column names I've pulled out of the ProductAttribute table, so the user would see a view with columns inseam, waist, color, cuffed, for example?
I'm guessing the answer is no, since I'll need to collect values from multiple rows of the ProductAttributeValue table for each row of the view. Would a stored procedure allow me to create a result set whose column names and data types are only determined at execution time? I'm currently using Oracle, if that matters.
Does anyone know of other RDMBS solutions to using metadata like this in the literature? Any help, ideas, or simpler approaches would be greatly appreciated.
Thanks!
Bob
Product
productKey
productTypeKey
quantityOnHand
ProductType
productTypeKey
name
ProductAttribute
productAttributeKey
name
valueType -- 1=boolean, 2= float, etc.
productTypeKey
ProductAttributeValue
valueBoolean NUMBER(1)
valueFloat FLOAT
valueInteger NUMBER
valueString VARCHAR2(4000)
productAttributeKey
productKey
A Product represents a quantity of identical items of a specific ProductType (navy men's slacks, 32" waist, 34" inseam uncuffed, for example). For each ProductType, the ProductAttribute table defines a set of attributes supplied at runtime by users of the application. For example, they might create a ProductType named 'PantsCo Slacks' that has ProductAttributes named 'inseam' (float), waist (float), 'color' (string) and 'cuffed' (boolean). The values of those attributes for a particular Product (32.5, 34, 'navy', 0) are then stored in the ProductAttributeValue table, one row for each value.
(By the way, this is a toy example--the real problem could have dozens of attributes for each product type, and thousands of product types, hence the need for this kind of approach.)
Managing this indirection is straightforward in a programming language, but I also need to make things easy for people using SQL directly. So here's my question (finally!): is it possible to create a VIEW (using dynamic SQL) with column names I've pulled out of the ProductAttribute table, so the user would see a view with columns inseam, waist, color, cuffed, for example?
I'm guessing the answer is no, since I'll need to collect values from multiple rows of the ProductAttributeValue table for each row of the view. Would a stored procedure allow me to create a result set whose column names and data types are only determined at execution time? I'm currently using Oracle, if that matters.
Does anyone know of other RDMBS solutions to using metadata like this in the literature? Any help, ideas, or simpler approaches would be greatly appreciated.
Thanks!
Bob
