Many to Many design

larryk

Well-known member
Hello,

Below is the correct schema, correct?

Home Table
ID
house_name

Home_Offer Table
ID
Home_ID
Offer_ID

Offer Table
ID
Offer_Amount


The idea is that many Homes can have many Offers.

When looking at the Home table record, the master/detail shows the Home_Offer record (all good).

When looking at the Home Offer table record, the 2 master/details configs (need both, Home and Offer)...
it is not showing up correctly???
1) it only shows the Home record, but it is the WRONG record? Have no idea how it shows the wrong record?
2) the Offer record is NOT showing. Not sure why this isn't showing.


I've looked at the simple data values, etc. multiple times... it is correct.
PLEASE do this same setup/schema and let me know if it works for you?
I'm sure it will not?
BUT if it does... I will find out my error?

NOTE: I already posted early, that using a view, does not allow you to update 2 tables :(
and My end resulting goal is to ALLOW users to search for Homes and then easily see/edit Offers.

Is there another way to accomplish this?

THANKS!!!
 

larryk

Well-known member
Your current DaDaBIK version
You are using DaDaBIK version 10.1-Manarola enterprise, installed on 07-01-2020 (installation code: 157025efd31de5bb7d), the latest version of DaDaBIK is 10.1-Manarola released on 03-09-2020

You are runnning the last release of DaDaBIK

System info
PHP Version: 7.2.31

mysql version: 5.5.65-MariaDB

Web server: Apache

Client: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 Safari/537.36
 

eugenio

Administrator
Staff member
Hello,
I am not sure if I have well understood and I don't know your form configuration but if Home_Offer is the "details" table in a master/details view with Home, it cannot be, at the same time, also the master. When you access to Home_Offer, you should see it as a "normal" table with two lookup fields.

The home table doesn't even have a reference to the ID of Home_Offer (and it's ok it hasn't), so it cannot be the details in a master/details form.

Best,
 

larryk

Well-known member
Thanks, Eugenio!

1) I do not want to show the Home_Offer table to the end-user, but with Dadabik, it seemed I was forced too? Am I wrong?
2) Home is the master and Home.ID is the primary key for Home (master table)

Similar, Offer is master and Offer.ID is the primary key for Offer (master table).
So the Home_Offer is the intermediary table so "many to many" can be accomplished.

3) The end result is to SHOW and ALLOW offers to be edited when you look at Home records.


PS. about the error/bug/or odd record showing up in the Home Offer details... I have no idea how that "odd" record is showing... it is like a random record is showing?
 

eugenio

Administrator
Staff member
Without showing the Home_Offer table there is no way for the user to set which are the offers related to a house.
So you just have to set Home_Offer as the details table for Home (master).

About the odd record, I think don't understand what you mean; Home_Offer is just a normal table and you will see the records you have inserted there, there isn't a "home" record and an "offer" record.
Home_ID and Offer_ID should be two select_single lookup fields.
Do you mean the Offer_ID field is empty? You set a particular value and you cannot save it?

Best,
 

larryk

Well-known member
so anyway, I'm changing this specific situation to only use 2 tables (not the intermediate table). While the 3 tables could be used, for this exact relationship, I see it isn't required.

However, I would love to have Dadabik be able to handle the true Many to Many schema.
Where the middle linking table is not really seen, but records can be shown/searched and editable.

thanks
 

eugenio

Administrator
Staff member
I am not sure but maybe this is what you are looking for:

https://dadabik.canny.io/feature-requests/p/many-to-many-relationships-normalized-schema-using-checkboxes-selectmultiple

Best,
 

larryk

Well-known member
YES... exactly that :)

But as the other people in that thread, in the current state of Dadabik, it is not possible to handle Many to Many relationships AND be able to edit/insert any of the data.

note: I did create a view and the view can provide some of the extra benefits/features one would need, it has its limitations as well.

FYI. Just saying.. Dadabik is wonderfully good at what it does... better than most (or all?) of the same/similar competitors.
But adding the ability to process a "many to many" relationship (ie. hiding the intermediate table to users) would truly give any appllication the power it needs.

ALSO, making it easy/simple to pass parameters (ie. primary keys, or any field)... so custom PHP can be written if needed... without jumping through hoops. While Dadabik can't handle ANY feature or process one needs in their app, but if you provide easy access for people to write their own code would make Dadabik... perfect!!!

Meaning, I want to use your existing features/processes/GUI, etc. BUT I also need to add/insert my own special logic at times.
MOST tools to do not allow "custom integration" easily. Really, they make it so hard, it's not fun use.

thanks for listening
:)
 
Larry,

We are with you on your M2M sentiments. Please vote on the link Eugenio replied with in the canny.io page so maybe we can get enough user interest behind this feature request to make it into a future release. Making the intermediate table invisible to the process of M2M table relationship record updates would truly be a game changer much like the form configurator does with field lookups from other tables.

We currently use 2 master/details links on a central table fields with 2 intermediate tables with single select field lookups and a view for M2M support and it works okay for most cases, but the view query has to be very simple as complex joins will not work. Also, searching for data in the details tables is difficult as they cannot be seen from the master classic grid they are under. So that requires the user to resort to the intermediate tables in some cases for the GUI. Doing all this in a normalized format is a challenge as you realize. You can enter the data using these techniques but there are a lot of edge cases and it is far from seamless.

I think the product strike a good balance between low coding features and customization capability to meet users unique needs, but there is always more room for additional flexibility like you speak of. We too have looked at many products out there and this one is the closest to meeting both needs at a reasonable price.Please file a request in canny.io for the parameter passing and we will happily vote for it as I see the power that could provide to tailor the product in a generic way.

-Todd
 

eugenio

Administrator
Staff member
Hello,
I think it's better to clarify here.

Let's take a classic three-tables example:
courses (id_course, name_course, .... )
teachers (id_teacher, name_teacher, ...)
courses_teachers (id_course, id_teacher)

In this example, if courses is in master / details with teachers, you can see, for each course, which are the teachers involved.
Working on the courses_teachers (details table) allows you to add/delete/modify the teachers involved in a course.

In some cases, you might need to create a view for courses_teachers, if you need to show fields that are NOT in the teachers table but in other, related tables. You can still add/delete/modify if the view is updatable/insertable. The problem that Todd reported was related to the use of outer join, a view containing an outer join is not updatable/insertable but for the views you typically use in these situations, normally you don't need an outer join.

I am still confused about how Larry would like to represent the situation from the user interface point of view, though.
Let's forget for a moment the database schema.
I am in the edit page of a course, how should I add the teachers involved (teachers who are already available in the table teachers, because they also teach other courses)?

Best,
 

larryk

Well-known member
So to answer you question:
"... how Larry would like to represent the situation from the user interface point of view..."

on the edit page of a course:
((meaning, you were on the results of courses-- the prior page was showing all courses and you selected one to edit))

simplest way/version--
just have a dropdown for teachers available.

BUT when the dropdown is selected and saved. Dadabik automatically saves BOTH tables:
the course table (obviously)
but also inserts the record of the intermediate table (this is hidden to the user).. it happens automatically.
the 2 IDs (course ID and teach ID) are saved in the courses_teachers table.

NOTE: one step further**, which is completely custom php, would be to easily allow the developer to add custom code to also save extra fields. ** this is next level thinking, but is very useful to make great custom applications. Dadabik just needs to provide "easy" to all the ideas created/used to the developer. Writing the custom php code is ALL ON the developer (me), but providing easy access to the IDs created or used, is what Dadabik should provide. Have Databik use session variables or global variables?

** this concept was what I last posted on the canny.io site

thanks!
 

eugenio

Administrator
Staff member
Hi Larry,
ok so yes, that's what I described in the canny post, the field must be a select multiple, though; a dropdown allows you to only select one teacher.

In case it's acceptable in your situation to use a de-normalized schema (no intermediary table, dadabik saves the teachers in ONE field of the courses table, separated by ~ e.g. teacher1~teacher2), this is already possible (select multiple checkbox or select multiple field type).

About the last part of your post, not sure if it helps in your specific case but what you describe is normally implemented using dadabik hooks.

Best,
 
Here is some additional input, to augment to Larry's request.. It would be nice if there was an option to used a normalized schema approach with the multi-select drop downs in addition to the current de-normalized schema technique.

The way I see this additional option working would be that if the normalized multi-select field type is chosen in the form configurator then additional properties would be available for that field (from the courses table) that specify the name of the intermediate table (courses_teachers), the name of the linked fields in the table (teacher_id, course_id), and the name of field from the table (teachers) to display on the grid (teacher name, teacher code, etc.) similar to how the single select operates for lookup tables or master/detail. The difference here is that we would use multi-select, and on the classic grid the multi-select field would then display fields for each of the multiple records as referenced from the intermediate table. Unlike the multi-select, it would not display a blank checkbox in every record for example.

On the edit form behavior, changing, adding, or deleting fields would then automatically save the field id in the intermediate table records without the user having to do this as a separate step in a separate table, thus achieving Larry's request. I know my explaining of all this is a little hard to follow but I think having the normalized multi-select field type that maintains the intermediate table id's and the master and detail table fields automatically for the M2M record relationships would be an outstanding capability. If M2M was easy to implement I' m sure it would already have been done by now ;-)

Thanks,

-Todd
 

eugenio

Administrator
Staff member
Hello Todd,
your explanation was clear and yes, that would be an option; as you said, it's a feature that requires a lot of work and testing and the planning of new features always takes in consideration a combination of: my personal vision, the requests I got on canny, the cost of the implementation and the added value. Some features are also implemented because some customers sponsor their development.

This feature could certainly be useful, but if you need to see many fields of the "linked" table (the teachers of the courses) I don't think the select multiple is the right field (from the point of view of the UI) and a master details view would be much more clear.

I am not sure why you said this feature would not display a blank checkbox in every record; if you don't have any linked record you should be able to leave the field blank.

PS I will move this thread to the general discussion forum and change the title, I don't see any bug here.
 
Eugenio,

I agree with your take on the UI design if there are many fields to be shown on the grid that the master details is a superior approach.
As to my comments on the blank check boxes, I may be misremembering what I did some months ago, but I could have sworn that on the classic grid, when the field has a multi-select type that all the possible choices are always shown (selected or not), taking up space and making each row taller. My point here was that only records with linked selected fields should be shown in the grid to save on the screen space used. I'll have to re-check the demos as I may be mistaken on how the multi-select type is working in an M2M relationship.

If we were interested in sponsoring feature development like for this feature or others for example, how is that process initiated? I assume there is a process of requirements development and scope definition, etc to formulate a schedule or plan.Then I assume an hourly estimate would be put together to determine the total cost of sponsoring the work.

Depending on the cost and some other factors I may be able to get our management interested in implementing some new features in DaDaBik using this method. I just need to get an idea of typical costs to determine how to pitch this and communicate the value added verses the development cost to our teams to see if if would be worth pursuing.

Thanks,

-Todd
 

eugenio

Administrator
Staff member
Hello Todd,
about how the select_multiple* fields are rendered in the results grid: only the options selected are displayed so if there is no selection the cell should be just empty. If this is not the behavior you get, I think there is something wrong going on.

About sponsoring features: yes, it's more or less as you described but it might change case by case; if you want to talk about it more in details (before deciding if you want to pitch it) the easiest thing is to reach us using the contact form on the site.

Best,
 
Top