Alpha Video Training
Results 1 to 7 of 7

Thread: design - three level subbrowse

  1. #1
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,842

    Default design - three level subbrowse

    I am not sure how to structure the set(s) and forms for the following. In other programs, I would create nested subforms/browses to accomplish this. In Alpha, I can't tell if I would create one or two sets, and how to show that last tier properly.

    I have three tables:

    DocSet (Set_ID)
    DocVariation (Set_ID, Variation_ID - related to above on Set_ID
    DocFilename (Set_ID, Variation_ID, Filename - related to above on Set_ID + Variation_ID)

    A view of the data might look like this:

    DOC SET A
    - VARIATION A
    -- FILENAME: 123.DOC
    -- FILENAME: 456.DOC
    - VARIATION B
    -- FILENAME: 123.DOC
    -- FILENAME: ABC.DOC
    DOC SET B
    etc.

    This indicates that Document Set A is comprised of files 123 & 456 if Variation A is selected, and 123 & ABC if variation B is selected.

    I am trying to create a form where the user can browse to a Doc Set and view/edit the available Variations. And, upon selecting a Variation, view/edit the related Filenames. Basically, a three tier related browse. I can make this work fine for a two-layer situation, not a three.

    The attached graphic shows how it might look on screen. I left in the related fields to show relationship.
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  2. #2
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default

    Steve,

    It looks like you have a many to many relationship between DocVariation and DocFilename. I usually add an intermediary table so you would have:

    Code:
    DocSet
          ====DocVariation
                      =====DocVar_DocFile
                                    ------DocFilename
    DocSet (Set_ID)
    DocVariation (Set_ID, Variation_ID - related to above on Set_ID) .. 1-N
    DocVar_DocFile(Set_IDv, Variation_IDv, Set_IDf, Variation_IDf - related to above on Set_IDv + Variation_IDv=Set_ID + Variation_ID)..1-N
    DocFilename (Set_ID, Variation_ID, Filename - related to above on Set_ID + Variation_ID=Set_IDf + Variation_ID)..1-1

    While looking at this I realised that if DocVariation is only linked to one DocSet (one - many) then unless you have another reason to do so you shouldn't need the compound key in the other tables. This would simplify to:

    DocSet (Set_ID)
    DocVariation (Set_ID, Variation_ID - related to above on Set_ID)..1-N
    DocVar_DocFile(Variation_ID, FileName_ID - related to above on Variation_ID)..1-N
    DocFilename (FileName_ID, Filename - related to above on FileName_ID)..1-1

    You can still have the Set_ID in the DocVar_DocFile table for info but you wouldn't need it for links.

    The second browse would be based on the DocVar_DocFile table and have the fields of the DocFilename table included as well since it is a 1-1 relationship. Neither the Set_ID or the Variation_ID is needed in the DocFilename Table since that data will be shown in the browse from the DocVar_DocFile table.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default

    Tim,

    The second browse would be based on the DocVar_DocFile table and have the fields of the DocFilename table included as well since it is a 1-1 relationship.
    I'm having trouble seeing why these need to be two tables? Since the link is one to one, why not use a single table ?

    -- tom

  4. #4
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default

    Tom,

    The two tables are needed so that 1 DocFilename record can belong to multiple DocVariation records as in Steve's sample data
    DOC SET A
    - VARIATION A
    -- FILENAME: 123.DOC
    -- FILENAME: 456.DOC
    - VARIATION B
    -- FILENAME: 123.DOC
    -- FILENAME: ABC.DOC
    DOC SET B
    The filename 123.doc belongs to both variation A and B. Without the intermediate table you would need to duplicate the record and the only difference would be the value of the linking field(Variation_ID) The DocVar_DocFile table is only the mechanism to all multiple linking values to be assigned to one Filename record.
    DOC SET A
    - VARIATION A
    --Variation A, Filename1--- FILENAME1: 123.DOC
    --Variation A, Filename2--- FILENAME2: 456.DOC
    - VARIATION B
    --Variation B, Filename1-- FILENAME1: 123.DOC
    --Variation B, Filename3-- FILENAME3: ABC.DOC
    DOC SET B
    - VARIATION C
    --Variation C, Filename1--- FILENAME1: 123.DOC
    --Variation C, Filename2--- FILENAME2: 456.DOC
    Another benifit is that you can reverse the set using DocFilename as the parent 1-N to DocVar_DocFile 1-1 to DocVariation and find all the Variations where a particular Document is used.

    This is the way I have learned to handle many-many relationships. If there is an easier or better way, or just different I am all ears. :)
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

  5. #5
    Volunteer Moderator Steve Wood's Avatar
    Real Name
    Steve Wood
    Join Date
    Nov 2003
    Location
    Bay Area, California
    Posts
    8,842

    Default

    Thanks, your discussion pointed me in the right direction. I put my thinking cap back on and restructured the tables. Now I have the following:

    Table 1: Doc_Set - w/fields Doc_Type, Doc_State (previously 'variation') and Set_ID (unique, autoincrement)

    Table 2: Doc_Set_Files - w/fields Set_ID, Filename and Set_ID.

    Doc_Set to Doc_Set_Files is 1-N on Set_ID.

    That structure alone provides a list of Filenames assigned to any Document_Set, which is what I was trying to achieve. Where I went wrong was thinking there was a middle layer (Variations in my initial example) at all. A Doc_Set is a combnation of a document type and the state involved. There was no reason to break out the State as a child to the Doc_Set, it should be part of it.

    Purely for viewing purpouses, I also created Sets with primary tables Doc_Type and Doc_State so I can view by Type or State. Attached graphic is an example.

    Doc_Type to Doc_Set 1-N on Type_ID
    Doc_Set to Doc_Set_Files 1-N on Set_ID
    Steve Wood
    Join the ALPHA DEVELOPERS NETWORK
    There is no Cloud. It's just someone else's computer.
    Web - Mobile - Hosting - Products - Frameworks - Developer Resources
    AlphaToGo | IADN (100% Alpha Anywhere Websites)

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default

    Tim, thanks. In the initial post I missed the fact that the same grandchild record was linked to two different children. -- tom

  7. #7
    Member
    Real Name
    John Caro
    Join Date
    Aug 2007
    Posts
    1

    Default Re: design - three level subbrowse

    I'm interested in how one might set up a many to many "set"
    Does Alpha handle this? Or does one need 2 sets?

Similar Threads

  1. Security Level Question
    By cambriabiz in forum Web Application Server v7
    Replies: 3
    Last Post: 02-02-2006, 03:07 PM
  2. Security Level Rewrite
    By pruel in forum Archived Wishlist
    Replies: 1
    Last Post: 01-04-2006, 07:14 PM
  3. HELP at table level
    By John Gamble in forum Archived Wishlist
    Replies: 0
    Last Post: 02-25-2005, 03:08 PM
  4. Admin password level
    By dchiass in forum Alpha Five Version 6
    Replies: 2
    Last Post: 11-09-2004, 05:58 AM
  5. Low Level Warning
    By Keith Hubert in forum Web Application Server v6
    Replies: 1
    Last Post: 08-09-2004, 06:58 PM

Bookmarks

Posting Permissions

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