Hi folks,

don't blame me for double posting but I haven't found and answer via search

I am trying to get my hands dirty with some DB usage. In fact I need a database (at least I think it is a good option) as a data storage for an application. But have a problem I can't solve and my old documents from university didn't help me either. How Do I map a Linked List?

Stupid example: There is a one to many relation between A and B, but 'n' isn't fixed at design time but rather at runtime. In my non-DB approach I just used a special token to indicate the end of the variable length field. But I don't how to do it for a DB. Well, I could simulate a linked list with foreign-keys but this is probably not the best way to do it.

Any ideas? Would be great help!

Foreign keys really are the best way to create the database equivalent of a linked list. Code can use databases, and databases are built from code, but there are significant differences between the two!

Welcome to the world of databases!

-PatP

Oh, that was a fast response. So I end up with a table of three entrys.

primary key | data | foreign key

where foreign key points to a another primary key in the same table. The end of the list would be foreign key set to null. Another table just needs to know the primary key of the first entry in the list. Is that right so far?

But how do I query this list? I know just the basic SQL commands. 'Select from where...' stuff like that.

Thanks in advance!

The bad news is that there is no 'standard" way to create that kind of query using SQL.

The good news is that virtually every SQL vendor has a proprietary solution that will get you what you need. For more details, I'd suggest that we adjourn to the appropriate database engine forum, or if you tell me which engine you are using I can move this thread there for you.

-PatP

actually, it is the first one in the "linked list" that should have a NULL foreign key

the foreign key points to the parent

this data structure is called the adjacency model

more info here, including sample queries: Categories and Subcategories

@r973
Thanks, this is exactly what I was looking for! But I think it's quite a bit of work to get it right. Is there known best practice when to use it? For example if you know the list won't exceed 10 entries then sticking with a mx10 table is ok despite the waste of memory for not totally filled rows.

@Pat Phelan
I haven't decided yet but an embedded database like Derby, H2 or HSQLDB looks like fun. Right know my app deals with some hundreds of records so there should be no need for a big, seperate db.

waste of memory for not filled rows?

don't worry about it!!