Re: Newbie Question on Schema Design

On Fri, 05 Dec 2003 11:02:08 -0500, Ravi <rg27_at_cse.buffalo.edu> wrote:

>I want to create a table which will enable me to query what file(s) a
>user provided name appears in. For this I want to create a table having
>an attribute (varchar(25)) for the name and a few other attributes for
>some additional information about the name (say part of speech,
>designation, etc). Most importantly I need an entry (column or
>attribute) which is essentially a linked list of filename(s) which will
>list all the files that the name (which is the primary index) occurs in.
>I intend to use Pro* C/C++ precompiler with a C++ program to generate
>the data and populate the tables but my question is how can I implement
>a linked list as an attribute of the table. I can define the linked list
>class with the methods, etc in C++ but how can I get Oracle to recognize
>this class. Also assuming it is somehow done I could do
>
>select *
>from wordtable
>where name like '%soft';
>
>would lead to the selection of all rows (complete with file names info)
>with words ending in soft. how would the sql know how to display the
>linked list i mean i could write a method for traversal but how would
>sql know how to use it.
>
>I am sorry if my questions seem vague. i am just getting started and
>this seemed a good way of designing the schema though i don't know yet
>how to implement it. any suggestions will be highly appreciated.
>
>Thanks,
>Ravi.

sql doesn't support linked lists. SQL databases are relational databases and relational databases are concerned with sets implemented as a table.
That said, you would either need to set up a separate table with 2 columns: a file_name and a linked_file_name, where of course the relationship will be automatically recursively. This can be dealt with by using the CONNECT BY operator. The other option would be to implement VARRAYs in your table. This might be more simple.

It seems however you are planning to develop a full text retrievel application and as far as I am aware Oracle is providing Oracle Intermedia, which is an application geared at Text Retrieval. It comes standard with the Enterprise Edition.

--
Sybrand Bakker, Senior Oracle DBA