PDA

View Full Version : design - three level subbrowse


ABC123

Steve Wood
06-13-2006, 03:58 PM
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.

Tim Kiebert
06-13-2006, 07:53 PM
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:


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.

Tom Cone Jr
06-13-2006, 09:22 PM
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

Tim Kiebert
06-13-2006, 11:14 PM
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 BThe 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. :)

Steve Wood
06-14-2006, 04:25 AM
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

Tom Cone Jr
06-14-2006, 07:10 AM
Tim, thanks. In the initial post I missed the fact that the same grandchild record was linked to two different children. -- tom

DesignGeek
08-24-2007, 01:25 PM
I'm interested in how one might set up a many to many "set"
Does Alpha handle this? Or does one need 2 sets?