Re: Data Structure Question

peter_at_mclinn.com wrote:
> I'm in the middle of creating a web invoicing system for my company
and
> want to try a new approach to an old problem.
>

Why? There are countless solutions implemented using existing methods.

>
> The each invoice our billing coordinators enter into our current
system
> dumps data into two table.
>
> The first table contains the date entered, invoice Number, Amounts
> paid...and like data. The second table contains line items with a fk
> on the incoive number. Very Simple, but the data grows fast in the
> second table rather rapidly.
>

This seems to be the perceived problem you are solving? Data storage?

>
> The records, after created, printed, and mailed are rarely ever
touched
> again.
>

Would it be a problem if on those rare occasions the process became more difficult and limited?

>
> So my question is this:
>
> When I'm programming the interface that dumps the data into Oracle
I'm
> thinking about creating just one table using the following structure:
>
> invoice Number, Amounts paid... and a CLOB field that contains the
line
> item data. (Varchar2 is not large enough.) The data that I will be
> dumping into this field will be stored in an XML format for easy
> retrieval at a later date if necessary.
>

So in an effort reduce data storage you are suggesting going from

prod qty price

book      1      15.00
flowers  12      50.00
toys      2      30.00
car       1   20000.00

to something like

<item><prod>book</prod> <qty>1</qty> <price>15.00</price></item>
<item><prod>flowers</prod> <qty>12</qty> <price>50.00</price></item>
<item><prod>toys</prod> <qty>2</qty> <price>30.00</price></item>
<item><prod>car</prod> <qty>1</qty> <price>20000.00</price></item>

Even given generously short attribute names, this does not appear to be shrinking the data in any way. In fact it is now looking larger (it is). Unless you are also sure you only have one or two lines on an invoice.

>
> Has anyone done this before,
>

Everyone was doing it in the sixties. Like most things once you forget how rediculous it looked it becomes popular again.

>
> and what is the disk cost of the clob
> datatype?
>

The same as any other data type, the more you store, the more it costs.

>
> Am I going down a bad path?
> We create some 70 invoices a week.
>

It probably doesn't matter at this volume.

Hth

-- 
MJB