Consisntent and inconsistent data type in subform

[jaryszek]

Hi Guys,

i created sample satabase and relationships like here:




So I have cats, and different cats can have Food or can have not food.

And these cats can have kitten.
And this kitten can have their own bowl or not.

I mean business model is not important here, because this is a technical question.

I created 2 forms : MainForm and QBowl Form.

On MainForm i have consisten data type (Dynaset) and when i am choosing Food automatically it is bounded to CatName.

On subform to link forms i to had to create inconsistent data type.

But when i am choosing BowlID (so color for bowl) the juntion table between CatsChildren and Bowls are not fullfilled automatically.
How to solve this?

Example:
I am choosing Cat "Minni". I am adding to her food "No Name".
Now i am adding "Kid1" to her.
Automatically field CatsIDFK in subform is fullfilled with CatsID from MainForm.



But when i want to add to her Bowl "Green", there is not automatically update in CatChildId in CatChildrenToBowl table:




When i set data from inconsistent to Dynaset in subform, the field CatsIDFK will be fullfilled automatically. But subform and mainform will be not linked...

How to solve this? what is yours best approach?

Please help,
Jacek

[Minty]

A kitten is still a cat, so instead of two tables you should have a single cats table and a IsChildOf field with an id of the parent cat.
Self referencing like this solves many undesirable effects of having two table with essentially the same info.

With your current design you would need to have a second table to store the cats bowls and another one for the kittens food.

Think of an employees table with an EmpID and with a field for the line managers ID.
You wouldn't have a seperate managers table, because they are also an employee so you can simply store the Managers EmpID no in the Employees table.

You might have a Joining table with the EmpID, ManagersEmpID, ManagerStartDate, ManagerEndDate so that you had a history of who did manage who.

[jaryszek]

Thank you Minty,

your answer will be helpful for future.
But as i said this is only example to show what technical problem i have in other model.

I am not focusing on data model right now, only for relationships and forms like here. You can instead of Kitten add table mouse there.

---------------------------

Maybe i should use subform after event macro? When any of field will be updated automatically input CatsIDFK ?

Best,
Jacek

[Minty]

A number of points, some about overall design, some about your issue;

I think your problem here is the use of a junction table.
It appears unnecessary with this data model. You have Bowls, you should simply save the bowl ID as a fk with the kitten details.

The junction table is not adding anything other than complication.

You have overnormaiised the model I think, unless a kitten can have multiple bowls ?
And I know this is only an example, but I think it bears thinking about.

If there is a one to many relationship then you only need to store a FK in the related parent table.
If there is a Many to Many relationship then you require a Junction table.

If you only want unique records in the Many to Many table then your primary key should be a composite on the two FK ID's, in fact this is often preferable.

Your use of simply ID in all your primary keys is really confusing, as is having a form and a query called QBowl... (I know it was only an example)

The main problem you have is that your aren't populating the correct tables with your sub form.
You should be populating the Cat Children table with the Kitten ID, then (assuming you have multiple bowls for a kitten) have another sub form for the bowls to kitten junction table.

You haven't "cascaded" your forms to match your data structure.

[jaryszek]

Hi Minty,

thank you

A number of points, some about overall design, some about your issue;

I think your problem here is the use of a junction table.
It appears unnecessary with this data model. You have Bowls, you should simply save the bowl ID as a fk with the kitten details.

The junction table is not adding anything other than complication.

It has one purpose - to avoid nullable FK keys.
Kitten can have multiple bowls, but also can not have any bowls.

If there is a Many to Many relationship then you require a Junction table.

it is many-to-many relationship.

The main problem you have is that your aren't populating the correct tables with your sub form.

What if i have one additional table here?



So every kitten can have multiple toys.

So on forms i would add subform to QBowl form.
so in this case there would be not place for subform for kitten bowls. This should be done automatically without adding any subforms.

I found technical way to solve this.

On subform Bowl i used:

Private Sub Form_BeforeInsert(Cancel As Integer)


    If Nz(Me.CatChildID.Value) = "" Then
       Me.CatChildID.Value = Form_MainForm.CatsID.Value
    End If


End Sub

Thank you for help,
Jacek

[Minty]

That shouldn't be necessary if you set the Master Child properties correctly.
You can have no records or many. I'm not sure what the issue is with nullable records ?

In your original example you brought the bowl table into the forms query for the CatChildrenBowl form. It isn't required.
You simply need the BowlId in the combo source for the bowl.

You can have multiple sub forms relating to a main form. It is a common technique.

[jaryszek]

Hi Minty,

In your original example you brought the bowl table into the forms query for the CatChildrenBowl form. It isn't required.
You simply need the BowlId in the combo source for the bowl.

I do not understand it entirely. You mean i should use combobox only in Main form?

Can you show an example?

--->

I added to subform QBowl Subsubform CAtToys.

Still you would breake up Bowl table into separate form here?
So to have Bowl subsubform and subsubCatToys form bound to QBowl Subform?



Best,
Jacek

[Minty]

I'm afraid I'm too busy to create you a sensible example at the moment, however try the following

Rename your QBowl Form to subfrmKittensData as that is what it is really for.
Without that you can't create a kitten record against the parent Cat record.

Now In your Kittens Form you really need two sub forms. One for Bowls for that Kitten record and One for Toys.

Does that make sense? I believe there is a limit to the number of nested forms you can get to but I'm not sure what it is.

[jaryszek]

Yes, this make sense.

thank you Minty,

So on Main Form i can have Food automatically input into table using form. And i do not have to build subform.
But when i want to build the same query for Bowl for CatChildren Form - i have to break this down to 2 subforms in order to bind CatChildren Form with Main Form...

Everyone agrees?

Best,
Jacek

[Minty]

You would have a separate main form for creating your Food table entries.
Food types are independent of the Cat records.
You may have a Food that isn't used by any Cat.

You would have another subform on the Cat form for the Cats Food table as it is a one to many relationship. (You need a Cat record before you can record Food used for that Cat)

[jaryszek]

Minty,

thank you very much!

hmm this makes sense.

But Cats --> CatsToFood --> Food it is not one to many relationship, but many to many relationship.

In case one to many relationaships i could use CatsID as primary Key and create FoodIDFK in Food table and create one to many relationships between them.
But here i would have nullable FK. So not every cat would have Food.

I am little confused.

Best,
Jacek

[jaryszek]

Minty,

i think i understand model here.
How to build it in optimal and best practice way.

One more thing to solve here:

I think your problem here is the use of a junction table.
It appears unnecessary with this data model. You have Bowls, you should simply save the bowl ID as a fk with the kitten details.

i recreated relationships like here:



Now we have 2 thing here:

1) nullable FK when Cat doesnt have the bowl.
About nullable Fields and database here: https://softwareengineering.stackexc...an-intersectio
Developers are divided into 2 opposite sides: using it or not...

2) What if for CatChildren i would have bunch of characteristics?
Not only name, but color, eyes, lenght. It would be in the same table CatChildren?

Best,
Jacek

[Minty]

1) No, to my mind there is a definitive answer. If you need a many to many relationship you must use a junction table.

2) If the characteristic is a single value (in this case these are direct properties of the Kitten, it can only have one colour, length), then yes they should be stored with the KittenData.
If they are from a fixed list of options then you could / maybe should use a lookup table for say colours as it will keep your description to a fixed set of values, and store the items FK.

[jaryszek]

Thank you Minty,

one more thing here:

2) If the characteristic is a single value (in this case these are direct properties of the Kitten, it can only have one colour, length), then yes they should be stored with the KittenData.

But for example.

So we have Kitten: Donald (not Trump:P).
Donald has length = 50 cm, and color = brown.

And Donald has 3 different bowls: ID:1 = green, ID 2 = red, ID 3 = white.

So i will have in table CatChildren:

Donald, 50 cm, brown, BowlIDFK = 1,
Donald, 50 cm, brown, BowlIDFK = 2,
Donald, 50 cm, brown, BowlIDFK = 3

So i am clearly repeating the same data.
This is a surely good approach ?

Best,
Jacek

[jaryszek]

Anyone?

Jacek