Jerry Hayes wrote:

>
> Funny, I store voice files but would never keep them in the database, (keep
> them linked by ids and file locations)

Seriously, that works great until the user accidentally deletes or moves
the file you're tracking; when it happens, your database integrity is
shot. Also, if you have a need to encrypt the data, you have to manage
the encryption process of the external files manually.

> But seriously, the XML I keep with SQL 2005 are smaller structure files,
> maybe about 20-30 nodes.  It's just that if I didn't keep it dynamic there,
> I'd end up with (no kidding), hundreds of different data tables, just for
> keeping dynamic info.

You could easily do this with a ftMemo column. You could even abstract
its access into an object with methods to do the search on the ftMemo
and return the results. You would never have to work directly with the
table if you didn't want to.


>
> One of those different strokes things
>

I agree.

> Seriously, that works great until the user accidentally deletes or moves
> the file you're tracking; when it happens, your database integrity is
> shot. Also, if you have a need to encrypt the data, you have to manage the
> encryption process of the external files manually.

Agreed, but for the way we work, those files are high access; streaming the
voice out through multiple application serviers and not the database makes a
lot of sense.  We add about 10GB of new audio a day and serving that up from
our data server wouldn't work without a ton of additional hardware.  We also
keep some of the audio up at Amazon's S3 service (for cheap storage) and
track the file by guid in our tables.

Distributing these large blobs (audio) gives us a great fail-over process
for the larger, heavy to move stuff.  The database is then much easier to
backup, replicate, etc., because it's lighter.

>> But seriously, the XML I keep with SQL 2005 are smaller structure files,
>> maybe about 20-30 nodes.  It's just that if I didn't keep it dynamic
>> there, I'd end up with (no kidding), hundreds of different data tables,
>> just for keeping dynamic info.
>
> You could easily do this with a ftMemo column. You could even abstract its
> access into an object with methods to do the search on the ftMemo and
> return the results. You would never have to work directly with the table
> if you didn't want to.

Sort of what the XML does, you have the XML column, which is basically a
large string or memo.  The Xml column natively supports xpath, xquery, etc.,
just like you mentioned about abstracting the memo.

So, each one of my db records tracks the unique guid and common search
fields, each record has the dynamic columns stored in the xml field.

BTW, not in *any* way trying to be argumentative here  Just following up
on comparitive processes.

Jerry Hayes wrote:

>
> Agreed, but for the way we work, those files are high access; streaming the
> voice out through multiple application serviers and not the database makes a
> lot of sense.  We add about 10GB of new audio a day and serving that up from
> our data server wouldn't work without a ton of additional hardware.  We also
> keep some of the audio up at Amazon's S3 service (for cheap storage) and
> track the file by guid in our tables.
>
> Distributing these large blobs (audio) gives us a great fail-over process
> for the larger, heavy to move stuff.  The database is then much easier to
> backup, replicate, etc., because it's lighter.
>

So, your application gives the end user limited control of the files.
You are, in effect, extending your database via your software to include
parts of the disk controlled by the OS and then having your application
limit access to those areas. Your requirements allow you this
methodology. Neither of us can take the chance on letting our users
delete or move my media files. We just have different ways of
controlling access to them, which is afforded by the delivery of the
application. My user installs the application on his desktop and you
deliver your audio via a strict interface to another computer system.

>
> Sort of what the XML does, you have the XML column, which is basically a
> large string or memo.  The Xml column natively supports xpath, xquery, etc.,
> just like you mentioned about abstracting the memo.
>
> So, each one of my db records tracks the unique guid and common search
> fields, each record has the dynamic columns stored in the xml field.
>
> BTW, not in *any* way trying to be argumentative here  Just following up
> on comparitive processes.
>

Me either. I'm always open to new ways to process and store data.

It would be interesting to support xml via functions added to the
database. Maybe stored procs could be used here?

Mark

>
> But seriously, the XML I keep with SQL 2005 are smaller structure files,
> maybe about 20-30 nodes.  It's just that if I didn't keep it dynamic
> there, I'd end up with (no kidding), hundreds of different data tables,
> just for keeping dynamic info.
>
> If my XML structures were large and my data was mildy variable, wouldn't
> do it that way.  But, am *shocked* by how well it works in Sql 2005; very
> fast, very searchable for the off times where it's need to be searched
> upon.
>

I use XML fields for the exact same purpose - and the fact that XML support
is built into the system and I can combine SQL with XML query is just
fantastic. Not that it can not be done manually with a MEMO field, it is
just that it is very easy to use in SQL Server 2005.

> It would be interesting to support xml via functions added to the
> database. Maybe stored procs could be used here?

I think it would have to be new custom functions added to the server core.
I'm mostly a MS database person, looking at DBISAM for my smaller customers,
(still millions of rows, just not tens of millions of rows).

So I could be wrong here about methodology.

> I use XML fields for the exact same purpose - and the fact that XML
> support is built into the system and I can combine SQL with XML query is
> just fantastic. Not that it can not be done manually with a MEMO field, it
> is just that it is very easy to use in SQL Server 2005.

Yes, the implementation is pretty nice; and due to the dynamic nature of my
data, not using typed xml nor xml indexes.

But of course, back to one of the original points, if you've got those
search fields exposed that handle 95% of your searches, hitting the XML
directly for the narrowed down fine tuning isn't bad at all.

Mark,

<< It would be interesting to support xml via functions added to the
database. Maybe stored procs could be used here? >>

Yes, custom SQL functions in DBISAM could be used to add XML parsing, etc.
as needed:

http://www.elevatesoft.com/dbisam4d5_customizing_engine.htm

--
Tim Young
Elevate Software
www.elevatesoft.com