Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 15 total
Thread StringList Name/Value Pairs in a Table
Thu, May 24 2007 9:18 AMPermanent Link

"Johnnie Norsworthy"
If I store a stringlist (BLOB) in a table of the form:
Name1=Value
Name2=Value
(about 400 more, "virtual fields")

Is there an easy way to work with the individual name/value pairs, possibly
going as far a using DB-aware controls? Basically, I need to implement my
own routine whenever a virtual field is read or written, probably including
some field-type properties.

I have a considerable amount of sparse data and this would be an excellent
way of storing it efficiently. I would create real fields from some of the
name/value pairs for indexing and searching.

-Johnnie

Thu, May 24 2007 5:25 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Johnnie,

<< If I store a stringlist (BLOB) in a table of the form:
Name1=Value
Name2=Value
(about 400 more, "virtual fields")

Is there an easy way to work with the individual name/value pairs, possibly
going as far a using DB-aware controls? >>

The quickest way to deal with this is via the TDBMemo control and accessing
the Public Lines property as needed.  Since it is a TStrings object, you can
use the name-value pair functionality in it to do the dirty work.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, May 25 2007 8:09 AMPermanent Link

"Charles Bainbridge"
We implemented such a scheme some years ago, including crude data-aware
controls. We finally concluded that the lack of flexibility by not being
able to access the data via ODBC, report writers, and other third party
tools outweighed the advantages. We've abandonned it and everything is back
in native table data columns. Disk space is relatively cheap... or consider
an architecture change - a detail table perhaps, where 'name' is a key
value?
--
Charles Bainbridge
"Johnnie Norsworthy" <jln206@verizon.net> wrote in message
news:128FF779-5594-47AA-8FCB-E84806B1A44B@news.elevatesoft.com...
> If I store a stringlist (BLOB) in a table of the form:
> Name1=Value
> Name2=Value
> (about 400 more, "virtual fields")
>
> Is there an easy way to work with the individual name/value pairs,
> possibly going as far a using DB-aware controls? Basically, I need to
> implement my own routine whenever a virtual field is read or written,
> probably including some field-type properties.
>
> I have a considerable amount of sparse data and this would be an excellent
> way of storing it efficiently. I would create real fields from some of the
> name/value pairs for indexing and searching.
>
> -Johnnie
>
>
Fri, May 25 2007 9:30 AMPermanent Link

"Johnnie Norsworthy"
"Charles Bainbridge" <charles.bainbridge@ntlworld.com> wrote in message
news:2859E1FC-5E15-49B3-896A-4959A9932E92@news.elevatesoft.com...
> We implemented such a scheme some years ago, including crude data-aware
> controls. We finally concluded that the lack of flexibility by not being
> able to access the data via ODBC, report writers, and other third party
> tools outweighed the advantages. We've abandonned it and everything is
> back in native table data columns. Disk space is relatively cheap... or
> consider an architecture change - a detail table perhaps, where 'name' is
> a key value?
> --
> Charles Bainbridge

I know disc space is cheap, but being able to accomodate a change in
structure without database updates is a great feature I used to have in my
DOS application that I miss sorely. Plus, I think it would lend itself to
replication to portable computers much better. I'm talking about a file so
sparsely populated that 143 meg compressed to 10.

What I might do is dynamically create calculated fields for the tables for
the name/value pairs for ReportBuilder (and other data-aware components) to
use.

Still thinking about this. I really miss the ability to "add" a field
without a restructuring process at my customer sites.

Fri, May 25 2007 10:46 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Johnnie


Why not just use a memory table. Keep a master template for all the fields as the fields bit of an sql create so you can create the necessary table. Store the value pairs (only where a value exists) in a memo field in the real table. Populating the memo table is easy

1. delete the existing record
2. insert a new record
3. grab the value pairs into a stringlist
4. for..next through the stringlist memorytable.fieldbyname(bit before the =).asstring := bit after the =
5. Post the record

You can also use TEXTSEARCH in filters/sql (I replace all #32 with #160 to make each value pair a single "word")

Roy Lambert
Fri, May 25 2007 12:18 PMPermanent Link

"Johnnie Norsworthy"
"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:810ED953-42A7-4AB7-8FC7-00733EA571FA@news.elevatesoft.com...
> Johnnie
>
>
> Why not just use a memory table. Keep a master template for all the fields
> as the fields bit of an sql create so you can create the necessary table.
> Store the value pairs (only where a value exists) in a memo field in the
> real table. Populating the memo table is easy
>
> 1. delete the existing record
> 2. insert a new record
> 3. grab the value pairs into a stringlist
> 4. for..next through the stringlist memorytable.fieldbyname(bit before the
> =).asstring := bit after the =
> 5. Post the record
>
> You can also use TEXTSEARCH in filters/sql (I replace all #32 with #160 to
> make each value pair a single "word")

Roy,

This might just work. I'll have to noodle on the details for my application.

Good idea - THANKS!

-Johnnie

Fri, May 25 2007 1:41 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Johnnie


Its very similar to what I do in a couple of apps, but I treat each value pair as a record not a field.

Roy Lambert
Fri, May 25 2007 1:46 PMPermanent Link

Bill Mullen
Johnny,

Instead of using hundreads of name/value pairs in a blob field why not
design your tables so that you have a name/vair pair table such as the
following image shows.

http://www.flickr.com/photos/8561575@N08/513678045/

This design allows you to add new fields as you need.  It also allows
you to add only those fields you need versus having to add every field
for every master record.

You can probably tweak the design to better suit your needs.

"MasterTable" is self explanitory - this is the master table where you
keep the main portion of your data.

"DataTypes" allows you to define what the data fields and types are,
for example, string, integer, float, blob, image, memo, etc.  You can
even use this to store structured data, for example say you get an XML
document and want to store the entire document as is, the datatype
could be XML.  

"DataTypes" also holds the NAME part of the Name/Value so that you
don't accidentally create two data elements (of different types) with
the same name.  This does not prevent you from inserting multiple rows
of the same data name for the master record if needed (more on this in
a moment).

"NameValues" stores the actual value of the data and the foreign keys.
Generally all values will be stored in the FieldValue column.  If you
have string data that won't fit, you might store the first 50
characters for indexing purposes and the entire string would then go
into FieldData and the DataType should indicate that the data uses the
blob (DataTypes.UsesBlob = true).

As you can see, this allows you to pratically store any kind of data
you want and can be expanded without redesigning the database at all.
Eventually, when it is time to update the schema of the database, it
should be fairly easy to move data out of this structure into the
MasterTable (or a child table of MasterTable).

You can perform searches for data types, data names, all data by
mastertable.id, etc.  I believe this is a much better design than a
stringlist with name/value pairs, especially if you might have upwards
to 400 name/value pairs per mastertable row.

Now about duplicates, you could place a table contraint on NameValues
that does not allow duplicate MasterTableID and DataID combinations.  

I threw this design together fairly quickly so that are certain to be
areas that can be fine tuned if needed.  One thing you may want to
look at for example might be to store UsesBlob in NameValues versus
DataTypes.  Then you would set it only if the data was too large to
fit in FieldValue, versus it being set just becuase of the DataTypes
definition.  

Hope this helps.


>If I store a stringlist (BLOB) in a table of the form:
>Name1=Value
>Name2=Value
>(about 400 more, "virtual fields")
>
>Is there an easy way to work with the individual name/value pairs, possibly
>going as far a using DB-aware controls? Basically, I need to implement my
>own routine whenever a virtual field is read or written, probably including
>some field-type properties.
>
>I have a considerable amount of sparse data and this would be an excellent
>way of storing it efficiently. I would create real fields from some of the
>name/value pairs for indexing and searching.
>
>-Johnnie
>
Fri, May 25 2007 2:05 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Bill


I thought I'd have a look but after creating a flickr account


Oops! You don't have permission to view this photo.


Roy Lambert
Fri, May 25 2007 2:26 PMPermanent Link

Bill Mullen
Well crap!  Wait one...
>Bill
>
>
>I thought I'd have a look but after creating a flickr account
>
>
>Oops! You don't have permission to view this photo.
>
>
>Roy Lambert
Page 1 of 2Next Page
Jump to Page:  1 2
Image