Best ways to store Text of indeterminate length

Best ways to store Text of indeterminate length

Post by Matthew Nichol » Wed, 19 Jul 2000 04:00:00



I'm designing a conversation board application and considering the best way
to store the Body of the posts.  They are of indeterminate length.  I expect
to grab each post (at least the Body) one at a time.  I also plan to do text
searching of them.  What I see as the options are:

1 Store the Body as a Text type field in the same table as the rest of the
post (Simplest option)

2 Store the Body as a Text type field in a separate table (Do I get a speed
advantage when I am searching the rest of the post if the Text fields are
not in the Table?)

3 Chop the Body up into some arbitrary length (say 255 chars) chunks and
store them as  char or varchar in a separate table along with contective and
ordering data.  Then when I need them grab all the chunks and put them back
together. (One of the nice things about this is that I can get the first x
number of characters of the body cheaply for a preview)

The big concerns that have me looking at this so closely is that I get the
impression that dealing with Text columns is system expensive and it is
important that this app scales well.

Any wisdom or advice is appreciated

Matthew Nichols

Dream Team Technologies

 
 
 

Best ways to store Text of indeterminate length

Post by Andrew J. Kell » Wed, 19 Jul 2000 04:00:00


Matthew,

    Text data is not stored with all the other data on the same page anyway.
Breaking it up would be a nightmare to maintain and implement. You don't
have to worry about the length of a text column since it will grow as big as
it needs to with no help from you. My advice is don't try and re-invent the
wheel. Just use the text column as is.

Andy


Quote:> I'm designing a conversation board application and considering the best
way
> to store the Body of the posts.  They are of indeterminate length.  I
expect
> to grab each post (at least the Body) one at a time.  I also plan to do
text
> searching of them.  What I see as the options are:

> 1 Store the Body as a Text type field in the same table as the rest of the
> post (Simplest option)

> 2 Store the Body as a Text type field in a separate table (Do I get a
speed
> advantage when I am searching the rest of the post if the Text fields are
> not in the Table?)

> 3 Chop the Body up into some arbitrary length (say 255 chars) chunks and
> store them as  char or varchar in a separate table along with contective
and
> ordering data.  Then when I need them grab all the chunks and put them
back
> together. (One of the nice things about this is that I can get the first x
> number of characters of the body cheaply for a preview)

> The big concerns that have me looking at this so closely is that I get the
> impression that dealing with Text columns is system expensive and it is
> important that this app scales well.

> Any wisdom or advice is appreciated

> Matthew Nichols

> Dream Team Technologies