[Dbix-class] solving problems with data driven design (was Re: Dynamic constraints on PostgreSQL arrays)

[Darren Duncan]

At 8:49 AM +0100 11/8/05, Kaare Rasmussen wrote:
>>Step back. What are you trying to achieve?
>
>Implement a multidimensional general ledger with flexibility in the 
>choice of the dimensions.
>There are other ways to do this, but all seem less elegant to me.

A much simpler, and more portable, solution is to use multiple joined 
tables instead.  If you want certain degrees of user-definable 
flexability, then have additional tables which contain meta-data, 
that the others join with.

This isn't quite the same problem, but here's an incomplete example 
schema for a simple but flexible product inventory system.  The same 
principles in its design can be used to solve your problem, where you 
get the flexability you need and with a simple elegance, which users 
would enjoy.

  product_type
   id
   label

  product_detail_type
   id
   product_type_id -> product_type
   label
   display_order
   input_constraint

  product
   id
   product_type_id -> product_type

  product_detail
   id
   product_id -> product
   product_detail_type_id -> product_detail_type
   value

Example data:

  product_type
  ------------
  1 Book
  2 Video

  product_detail_type
  1 1 Title 1 text
  2 1 Author 2 text
  3 1 Page Count 3 number
  4 2 Title 1 text
  5 2 Director 2 text
  6 2 Play Time 3 number

  product
  1 1

  product_detail
  1 1 1 There and Back Again
  1 1 2 Baggins
  1 1 3 1472

In these examples, the first 2 tables are meta-data.  The 
'product_type.label' lists values for a picklist on a casual user's 
main screen titled "add a new [foo] to the database", which would 
bring them to the form to add a 'product' and its child 
'product_detail'.  The 'product_detail_type.label' stores the labels 
used on that entry form where each field populates a 'product_detail' 
record .value; said labels can also be used on reports and other 
places.  Another admin screen in your program lets users populate the 
_type tables.

I suggest that a simple app implementing something like this would 
make a good demo for a database access module.

As far as I'm concerned, sticking to a purely relational storage 
model, where each intersection of a table row and column stores a 
single scalar value (or null), is an ideal solution for exploiting a 
database's strengths.

An object mapper module could give the illusion of storing arrays in 
fields, but that shouldn't be what actually happens in the database 
(though SQL:2003 object-relational databases would support such a 
thing).

P.S. I would have given you a ledger example, but I don't know 
exactly what they look like in your context.

-- Darren Duncan