SQL and table logic

[EchoLibre]

Hi,

I am tapping in my far too old knowledge of SQL and databases and… I’m stuck. I am missing something, and it “doesn’t click” in my brain…

So, I have few exercices, let’s say Exo_1, Exo_2, Exo_3, etc… with a description, some objectives, all good.

I have let’s cal theme categeories, in 3 layers, ie

Cat 1

Sub_Cat 1.1

Sub_Sub_Cat 1.1.1
Sub_Sub_Cat 1.1.2

Sub_Cat 1.2

Sub_Sub_Cat 1.2.1
Sub_Sub_Cat 1.2.2
Sub_Sub_Cat 1.2.3

Cat 2

Sub_Cat 2.1

Sub_Sub_Cat 2.1.1
Sub_Sub_Cat 2.1.2

Sub_Cat 2.2

Sub_Sub_Cat 2.2.1
Sub_Sub_Cat 2.2.2
Sub_Sub_Cat 2.2.3

Etc…

I want to be able to “add” mutliple categories to one give exercice

Ex. Exo_1 can have :
Cat 1 → Sub_Cat 1.1 → Sub_Sub_Cat 1.1.1
Cat 1 → Sub_Cat 1.1 → Sub_Sub_Cat 1.2.1
Cat 2 → Sub_Cat 2.2 → Sub_Sub_Cat 2.2.3

I created tables for :
Exercices
Categories
Sub_Cat
Sub_Sub_Cat

(i still have to ensure the correct Sub_Cat can be selected, ie. if Cat 1.2 is selected, I shouldn’t be able to pick a Sub_Sub_Cat 1.1.1 for exemple, but that will be the next step)

So, now, how to I link these ?

Cause I don’t know which exercice will have which categories, that’s why I’m going through all of this : I want to be able to add the categories “on the fly” to my exercices and then, I’ll be able to run reports per category for exemple (which will give me the exercices which fall into that category (and potentially other categories as well).

In other words :

• 1 exercice can have multiple categories
• 1 category will of course be linked to multiple exerices

I can’t get clarity if I shlould have the Cat, Sub_Cat and Sub_Sub_Cat Primary Key in the Exercice table, or the other way around, i.e. the Exercice Primary Key in the Cat, Sub_Cat and Sub_Sub_Cat table…

Any support on how to do this will be welcome

[RobertG]

Do you need one value for Exercises connected to Categories? Then you could connect from Exercises to many different Categories (1:n), but not from different Exercises to the same Categories (m:n).
If you need m:n you have to create a separate table to connect: “tbl_Ex_Cat” with “ExID” and “CatID”, where you could combine the primary keys from both.

[EchoLibre]

Sorry, the reply to your post lost all the formating, making it indigest to read So I answered to myself, but this is really a reply

[EchoLibre]

Table Link
I had this idea waking up, and I think you are pointing me in the right direction : I need “something” somewhat unique to link them, and since the connection between Exo and Cat is the (m:n) type, you’re right, I need an extra table to connect them using the ExoID AND the catID to be a unique PK.

Now, I have Cat, Sub_Cat, Sub_Sub_Cat.

The relationship is 1 Sub_Sub_Cat can only be linked to 1 Sub-Cat, which can be linked to only one Cat.

But one Cat can have multiple (yet limited to a few values) Sub_Cat, which can have multiple (yet limited to a few values) Sub_Sub_Cat.

So I’m gueeing that I need to have the Sub-Sub_CatID in the Sub_Cat table to link them, and the Sub_CatID in the Cat table to link them as well ? Or is it the Sub_Sub_CatID+Sub_CatID as a PK in the Cat table ?

So I probably need my extra table to contain ExoID + CatID + Sub_CatID + Sub_Sub_CatID to make a unique PK : can I have a PK using more than 2 fields ?

Form
I’ll then create a form to enster my exercice details, that part is ok.

In that form, I’ll have to also add potentially multiple Cat, Sub_Cat and Sub_Sub_Cat for one exercice… So I’m guessing that, behind the scene, I’ll have to ensure it creates 1 record per Exo+Cat+Sub_Cat+Sub_Sub_Cat combination, right ?

The way I see it, I’m thinking a “top” form with the Exo details, and probably a subform which I can click next on and enter new Cat+Sub_Cat+Sub_Sub_Cat combination while still keeping the same Exo on “top”, does it make sens ? So I don’t have to type again and again the whole Exo details just to add new Cat+Sub_Cat+Sub_Sub_Cat combination to the same Exo.

I’m not there yet, and I’ll do some test, but any pointers or thing to consider before rushing it through will be welcome.

And in the form, but this part should be the easiest, when I select a Cat, there is only a limited number of Sub_Cat values which should be displayed in a drop down, and same when the Sub_Cat is chosen, only a limited number of values in the Sub_Sub_Cat, but I guess that’s can be solved whith some IF conditions in the form itself.

Thanks again for puting some brainpower with me on this, that is really supportive

[EchoLibre]

In Fact, I can probably be “lazier/clever” and have the Cat and Sub-Cat populated based on the Sub_Sub_Cat, but that’s after solving the table challenge and table link challenge

Wait… I guess I got something here : instead of trying to “cascade down” Exo → Cat → Sub-Cat → Sub_Sub_Cat, I could work with ExoID and Sub_Sub_CatID, because based on what the Sub_Sub_Cat is, the Sub_Cat and the Cat can be “deducted”…

I still have the challenges linking multiple Cat/Sub_Cat/Sub_Sub_Cat to an Exo in the form (and therefore in the db)…

[RobertG]

Why don’t you create a little example?

1. Table Cat
   Field ID
   Field Text
2. Table Sub_Cat
   Field ID
   Field Text
   Field Cat_ID
3. Table Sub_Sub_Cat
   Field ID
   Field Text
   Field Sub_Cat_ID
4. Table Exo
   Field ID
   Field Text
5. Table rel_Exo_Sub_Sub_Cat
   Field Exo_ID
   Field Sub_Sub_Cat_ID

… but this only works if there is a value in Sub_Sub_Cat for every Sub_Cat and a value in Sub_Cat for every Cat.

[Villeroy]

The attached sample provides almost everything that can be used with relations like this:

EDIT: enhanced version of HSQL database with one more form and pictures illustrating how the table relations are mapped to form hierarchies.
ask112714.odb (247.8 KB)

[EchoLibre]

Brillaint !! I think that should work. There is indeed always a value in Sub_Sub_Cat for every Sub_Cat and a value in Sub_Cat for every Cat.

I’ll test this and will let you know

[EchoLibre]

I made great progresses thanks to your hints and recommendation

Now, I have a little hard time with my “updating” form…

I created a form with the Exo_Tbl and a sub form with a Query which makes the link between rel_Exo_Sub_Sub_Cat table and all the Cat, sub_Cat and Sub_Sub_Cat table and elements.

Working great to “view” info.

Now, if I’m not mistaken, for an “input” form, I would need to do somehing to update the rel_Exo_Sub_Sub_Cat table with new entries, since it’s what creates the link between everything.

If I add a sub-form with the rel_Exo_Sub_Sub_Cat and add a button with an action “new record”, that kind of work half way : it adds the Exo_ID from the Exo displaed in the main form,
but, if course, there is nothing in the Exo_tbl to add the Sub_Sub_Cat_ID in the rel_Exo_Sub_Sub_Cat table.

And here I’m stuck… I guess I can’t use an action button cause I need 3 actions :

1. New entry which create a new line in the rel_Exo_Sub_Sub_Cat table with the matching Exo_ID diplayed on the main form - that works.
2. Identify the Sub_Sub_Cat_ID from the slected Sub_Sub_Cat text
3. Put this Sub_Sub_Cat_ID in the rel_Exo_Sub_Sub_Cat table matching the Exo record displayed

I probably have the order of the steps wrong…

Ex. In rel_Exo_Sub_Sub_Cat I have
Field Exo_ID : 1
Field Sub_Sub_Cat_ID : 3 (which let’s say matches the text “red”)

For the Exo 1, I want to add another Sub_Sub_Cat : exemple “Blue” and let’s say
“Blue” Sub_Sub_Cat_ID is 5

When I click on my add reccord button, it adds a line in the rel_Exo_Sub_Sub_Cat :
Field Exo_ID : 1
Field Sub_Sub_Cat_ID : → There is nothing here, it is, of course, waiting for the Sub_Sub_Cat_ID

Now, I would like to have a combo box, with the list of Sub_Sub_Cat text, if I select “Blue”, then it knows the Sub_Sub_Cat_ID for this text is 5, and add it to the newly created entry in the rel_Exo_Sub_Sub_Cat table.

Make sense ?

How would you recommand I do that ? Not sure if I’m trying to take too many shortcuts, or on the contrary, making it more complex than it really is…

[RobertG]

Here a simple solution:
Main form for “Table Exo”. Subform for “Table rel_Exo_Sub_Sub_Cat”. Subform contains a tablecontrol with only one listbox for “Field Sub_Sub_Cat_ID”.
Code for Listbox

SELECT "Sub_Sub_Cat"."Text"||' → '||"Sub_Cat"."Text"||' → '||"Cat"."Text", 
"Sub_Sub_Cat"."ID"
FROM "Sub_Sub_Cat", "Sub_Cat", "Cat"
WHERE "Sub_Sub_Cat"."Sub_Cat_ID" = "Sub_Cat"."ID AND "Sub_Cat"."Cat_ID" = "Cat"."ID"
ORDER BY "Sub_Sub_Cat"."Text"||' → '||"Sub_Cat"."Text"||' → '||"Cat"."Text"

Create a form, which is a form beneath MainForm. It will be MainFormCat. Table “Table Cat”.
SubFormCat → “Table Sub_Cat” (Link to MainForm!)
SubSubFormCat → “Table Sub_Sub_Cat” (Link to SubForm!)
Every time you add values in this forms beneath the MainForm you have to refresh the listbox in the tablecontrol of the subform. So you could add the new entry in “Table Exo”.

I would put all Content of MainFormCat and SuuFormCat and SubSubFormCat also in tablecontrols.

[EchoLibre]

Hi,

Not sure I follow you…

My Cat, Sub_Cat and Sub_Sub_Cat are fixed. Not addition to be made here.

Only Exo to be either :
Created → that’s easy in a Main From
Updated → by adding new Cat, Sub_Cat, Sub_Sub_Cat

I guess I’m doing links where I should maybe not, and not where there should probably be…

1. Should the Main form with “table Exo” and the subform with “Table rel_Exo_Sub_Sub_Cat” be linked ?
   => I guess yes, with the Exo_ID

2. Nice SQL to display in the drop down , but I’m missing how to connect this : I tried to add it to the subform, I tried to create a subsubform (to link the combolist to the Table rel_Exo_Sub_Sub_Cat), but no matter what, although the info is properly displayed, it doesn’t update the Table rel_Exo_Sub_Sub_Cat with new (or additionnal) Sub_Sub_Cat_ID, ie it doesn’t create a new reccord in the “Table rel_Exo_Sub_Sub_Cat” to link a new Sub_Sub_Cat with a given exercice…

3. For the second part, with all the other Cat Forms and SubForms, I’m not sure I understand : is it a separate form (with the Mainform visually speaking) to display the various Cat, Sub_Cat, Sub_Sub_Cat ? Not sure I understand the purpose of this…

[RobertG]

Taken the example form @Villeroy and added a form, which sows what I have tried to explain:
ask112714.odb (257.7 KB)