How can a tree be stored in a database?

Question

If you needed to implement a tree using 4GL, for example C #, and save it in a database, for example SQL Server 2008, what does the schema / design look like?

In other words, what role will the database play in such an implementation?

Answer 1

Tree preservation

There are several options:

• In the end, it is just a tree, so you can store it just like any other tree (mainly through a recursive FOREIGN KEY).
• Or convert the suffix tree to an array of suffixes and save it to the database.
• Or you can serialize it (say) XML, and then save it in a single CLOB.
• , 20 , "" , , (, ).

. - , , , :

CREATE TABLE SUFFIX_ARRAY (
    ORDER INT PRIMARY KEY, -- Position in the suffix array.
    START INT NOT NULL, -- Position of the starting character of the suffix within the target string.
    LONGEST_COMMON_PREFIX INT NOT NULL -- If useful for your application.
)

"" (, CLOB ).

• , SQL.
• , SQL, .
• ( 4) CLOB ( ), , , ( ) - - ( ) .

Answer 2

.

Text
================
id  -- autoincrement
text  -- varchar

Text_Suffix
=================
startingTextId  -- fk reference to Text.id
suffixPartId  -- fk reference to Text.id

So... with this example data - 

Text
=================
1  |  lay
2  |  er
3  |  ing
4  |  s

Text_Suffix
==================
1  |  2
1  |  3
1  |  4
2  |  4

:

WITH All_Suffixes (id, text) as (SELECT id, text
                                 FROM Text as a
                                 EXCEPTION JOIN Text_Suffix as b
                                 ON b.suffixPartId = a.id
                                 UNION ALL
                                 SELECT b.suffixPartId, a.text + c.text
                                 FROM All_Suffixes as a
                                 JOIN Text_Suffix as b
                                 ON b.startingTextId = a.id
                                 JOIN Text as c
                                 ON c.id = b.suffixPartId)
SELECT *
FROM All_Suffixes

:

1  |  lay
2  |  layer
3  |  laying
4  |  lays
4  |  layers