Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    3

    Howto: runtime variable length fields

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Feb 2008
    Posts
    3
    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!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Posts
    3
    @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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    waste of memory for not filled rows?

    don't worry about it!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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