Re: Database Design - Handling Dynamically created products

On Fri, 6 Mar 1998 09:40:52 -0600, "Mauricio Zamora" <mzamora_at_telution.com> wrote:

>A. Everytime a new product is created, create the appropriate tables for
>the new product.
>+ Data integrity is high
>+ Applications accessing data is straight forward
>- Turnaround for creating a new product is low
>- Modification database during production is high

I've used this approach ( but only in a demonstration prototype ) to allow dynamic definition of objects and it seemed to work OK.

I used a Delphi program to allow the users to define the object which in turn created a table to hold its details, so turnaround time was short i.e. no developer / dba involvement was required. Difficulties arise in terms of space management and implementation of property changes. A data dictionary was used to dynamically build input windows in Delphi. It's difficult to ensure that the resulting input/query windows are aesthetically pleasing - I implemented a small form designer facility. The prototype allowed the definition of part of relationships between items.

This approach is a combination of A and C.

Data integrity is not as good as programmer defined tables/triggers/procedures, but turnaround time is very quick.

My experience is that most sites will not allow you to build an application that works this way because they have the idea that the database structure should be a rigid unchanging thing.

>B. Store configuration information for products as name/value pairs
>+ Turnaround for creating new products is low
>- Data integrity is lower (i.e. how do you maintain name/value pairs which
>vary in number across products)
>- Applications accessing data become more complex
>+ Modification of database in production is minimal
>

I've also used this approach. The big problem is performance.

I don't see that data integrity is lower, because you still need a data dictionary to define the meaning of the name/value pairs e.g. define a table to hold the definition of each property - name, datatype, low value, high value, list of values,...

Application complexity is high only if you want a friendly user-interface.

Database front end (e.g. reporting) tools don't readily support this structure.

>C. Store cfg data in the database as a blob
>- No way to query cfg data (really bad - generating reports would be
>impossible w/o creating our own reporting application)
>- Time involved in retrieving data is higher (i.e. retrieve, map to
>appropiate types/values)
>+ Turnaround for creating products is low
>+ Modification of database in production is minimal
>

If you're doing this, I don't see why you're using a relational database.

A fourth method is to use a generic table and map the names of properties onto column names i.e. a table product_orders could be something like:

	order_id number primary key
	product_id number
	property_1 varchar2(2000)
	property_2 varchar2(2000)
	property_3 varchar2(2000)
	property_4 varchar2(2000)
	property_5 varchar2(2000)
	property_6 varchar2(2000)
	property_7 varchar2(2000)
	...

with a product_properties table something like

	product_id number
	property	number
	datatype  varchar2(10) -- NUMBER, CHARACTER, DATE, ..
	low_value varchar2(100)
	high_value varchar2(100)
	list_of_values varchar2(2000) -- or another table

This structure is easy to implement. A view can be used to generate queries for specific products, mapping property names onto columns names.

Definition of new products could be limited to programmers who would define triggers/procedures to ensure data integrity. Generic triggers could be written to use the product_properties table to perform simple validation.

Any design purists will be really annoyed by this approach.          Received on Sat Mar 07 1998 - 00:00:00 CST