Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34

    Unanswered: Database Design Question

    Anyone know a good database scheme for storing formatted text? The text will be coming from XML in this type of format:

    <text id="01">
    <headline>Here is the title</headline>
    <subhead>Here is a subhead</subhead>
    <paragraph>Here is some text</paragraph>
    </text>
    <text id="02">
    <headline>Here is the title</headline>
    <subhead>Here is a subhead</subhead>
    <paragraph>Here is some text</paragraph>
    <subhead>Here is a subhead</subhead>
    <paragraph>Here is some text</paragraph>
    </text>
    ... etc

    How can I store in a database and keep the headlines, subheads, and paragraphs delimited without just storing the XML tags and keeping everything in order (each "text" may contain multiple paragraphs and subheadings ... )?

    I'm just wondering if anyone has done this before and what has worked for them ... just looking for opinions on the most efficient way to accomplish ... thanks!

  2. #2
    Join Date
    Oct 2003
    Posts
    12
    If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.

  3. #3
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    Originally posted by smorton
    If the data length will be less than 8000 characters drop the data into an a varchar column. If the data will exceed 8000 characters you will have to drop the data into a text column.
    Thanks for the reply. I'm looking for the most efficient way to set up relationships that will keep my text formatted the same way it is in the XML doc and in the same order. Any ideas?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Again, why don't you just drop this into a varchar or text field? Do you need to be able to query on different Tags? I don't get why you need to establish relationships to the XML.

    blindman

  5. #5
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    I need to be able to store other properties about each piece of text ... I guess this could be done with attributes of the XML tags. Is that the best way? Just dump the XML right into the db? Thanks!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What other attributes?

    How do you define a piece of text? One line? All the text between two tags?

    What about nested tags?

    blindman

  7. #7
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    There will be no nested tags ... no need for them at this point. Attributes will be font size, family, color, etc ... and a transition id.

    (I'm using ASP to generate XML for Flash ... but I also need to save the everything I write the XML in a database).

    So I guess I could just store in the db as follows:

    <text id="1">
    <head font="arial" size="12" color="000000" transition_id="1">This is the title</head>
    <sub font="arial" size="11" color="000000" transition_id="2">This is a sub title</sub>
    <para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
    <para font="arial" size="10" color="000000" transition_id="3">this is a paragraph</para>
    <sub font="arial" size="11" color="FF0000" transition_id="2">This is a sub title</sub>
    <para font="arial" size="10" color="FF0000" transition_id="3">this is a paragraph</para>
    </text>
    ... etc

    Does that sound right to you? Thanks!

  8. #8
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    (follow up) ... text is everything between 2 tags.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, you could have each line be an independent record containing both the text and the common attributes as separate fields. To keep the lines in order, use an incrementing key.

    blindman

  10. #10
    Join Date
    Sep 2003
    Posts
    522
    you can also identify all the attributes that define a piece of text and separate them into a set of tables that would contain key-based references, all grouped together by a page reference (if applicable).

  11. #11
    Join Date
    Feb 2003
    Location
    Denver, CO
    Posts
    34
    Thanks guys. This is kind of what I was thinking ... just wanted to get an expert's opinion. If I use both the incrementing key and the page id with several related tables then I can keep the data all seperated out into organized tables and carry all the required attributes. Then when I am ready to write to XML, for each page I just do a select across the related tables by page id and order it by the incremented order id.

    That brings the text out for each page in the right order with all of the formatting in place.

    Sounds right to me. Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •