New user: Intermediate tables and ForeignKey names

New user: Intermediate tables and ForeignKey names

Postby Drache » Tue Nov 22, 2011 9:28 pm

Hi,
I'm evaluating nuBuilder** for a backoffice software made from scratch*. I've already got started, read wikis, watched videos, created a primitive demo app and nuBuilder looks really efficient and promising! Especially, nuBuilder turned out to play nice with FastCGI behind the web server, and is easily "backupped". Some questions are left; can you tell me whether it will handle these two scenarios well:

-- 1. Our database as many n:n relations, i.e. it is full of that pattern:
TABLE customer
customer_id
cus_name

TABLE product
product_id
prod_name

TABLE bought # our intermediate table for the n:n relation - products bought by customers
bought_id
bou_customer_id
bou_product_id
bou_somerelationproperty

Let's add records. A user adding a "customer" record should see a list of "product". He must be able to select one (or some) related product(s) in the EditScreen.
Clicking SAVE should write both a new "customer" record and one (or more) new "bought" record.
There are promising "Lookup Objects". But these seem to require a "Field Name" under the Lookup Tab which obviously is a ForeignKey in the "customer" table. That field won't exist. Similarly, the "Subform" seems to require a ForeignKey in the "product" table, pointing to "customer". Am I right here ?
Can nuBuilder still handle this case well - probably, with with some custom SQL hooked in ?

-- 2. The wiki reads at Naming Conventions > Field Names:
"Any field that links to the Primary Key field of another table must be made up of the prefix used for the table it is in,
followed by the exact fieldname of the field it will be linking to, followed by the letters “id” (...)
e.g. If the primary table is ‘investor’ and you wish to link it with the ‘customer’ table: inv_customer_id"

It reads as a "must" convention but we have to violate it - there are multiple foreign keys to the same table. Will this break something ?

Thanks,
Ruben

+++++++++++++++++++++++
* we are a startup company, building an inhouse solution for customer relationship and knowledge management
** nuBuilder Version is 11.11.03
Related reading: viewtopic.php?f=4&t=28&p=64&hilit=new+user#p64
[EDIT: Corrected the statement about the ForeignKey entry in Subforms.]
Drache
 
Posts: 10
Joined: Mon Nov 07, 2011 11:42 pm

Re: New user: Intermediate tables and ForeignKey names

Postby Drache » Thu Nov 24, 2011 10:31 pm

Finally, I figured out one solution for my first question, and I can present the first reply :-D
It comes even without custom SQL, by just nesting a "Subform" and a "Lookup". Lets post it for the case of another new user stuck here.

Continuing the small example with customer and product, we need:

1. Prerequisites: There's a "Customer" form associated with the "customer" Table, a "Product" form for the "product" table, and a "Bought" form, yes indeed, for the "bought" table. ( I may confuse the names of a form and its associated table sometimes.)

2. We add a Subform into the "Customer" Form. This will manage our "bought" records.
Details: In the "Subform Tab" of the new Subform object, we refer to the "bought" table and to its primary key. The "Foreign Key" here is bou_customer_id. We specified 3 "Blank Rows" as in the tutorial video.
As a result, one of our foreign keys in "bought" (the bou_customer_id) will be maintained by the Subform.
(Remark: By adding a text object for bou_product_id into the Subform, we already could maintain the other foreign key manually now.
But this is not desirable. We will create a Lookup instead.)

3. We create a Lookup object. This for the remaining foreign key (bou_product_id) in the "bought" table.
Details: We enter bou_product_id as the "Field Name" in the "All Tab" of the new Lookup object. And we bind that Lookup to our new Subform.
The ID,Code and Description entries under the objects "Lookup Tab" are all fields of "product".
(Remark: It worked well to use the product_id for both ID and Code althought that generated primary key doesn't look nice to the end user.)
(Remark: We may add more objects into the Subform, e.g. one for the bou_somerelationproperty field.)
=>
The result: Lets use the Customer form.
Since we specified 3 "Blank Rows" in the Subform configuration, we see 3 blank rows to add "bought" records (and eventually, some already existing records.)
Each row has a Lookup input. Clicking here lets us select existing product(s).
The Lookup inputs are visible immediately in the Customer form, as desired - they are just framed by the Subform. This is comfortable and works fine.
I find it impressing how nuBuilder simply allows nesting these objects in each other. Great work !

(Tell me when I messed up something while translating real-world into the small "customer" example...)
Ruben
Drache
 
Posts: 10
Joined: Mon Nov 07, 2011 11:42 pm

Re: New user: Intermediate tables and ForeignKey names

Postby admin » Tue Dec 06, 2011 1:31 pm

Ruben,


-- 2. The wiki reads at Naming Conventions > Field Names:
"Any field that links to the Primary Key field of another table must be made up of the prefix used for the table it is in,
followed by the exact fieldname of the field it will be linking to, followed by the letters “id” (...)
e.g. If the primary table is ‘investor’ and you wish to link it with the ‘customer’ table: inv_customer_id"

It reads as a "must" convention but we have to violate it - there are multiple foreign keys to the same table. Will this break something ?

Its just a suggestion.


(Remark: It worked well to use the product_id for both ID and Code althought that generated primary key doesn't look nice to the end user.)


A lookup works best with records having an id, code and description.

The code field allows the entry of a code that is already known so you dont have to open a browse window with the magnifying glass.

But if you want to do it your way you could make the code width zero.


Steven
admin
Site Admin
 
Posts: 3079
Joined: Mon Jun 15, 2009 9:53 am

Re: New user: Intermediate tables and ForeignKey names

Postby Tinka » Thu Mar 27, 2014 8:53 pm

Thank you for explaining how intermediate or link-tables can be used with subforms. This general point is really missing in the Nubuilder documentation/examples.
It works fine for my db just like you explained. (although I am also using Inner join statements in the form sql).

But what do I do when I want to, in your example, add a non-existing product on the customer form, which uses this intermediate table? You can't use the lookup object here. Maybe a simple solution which I just overlook.

Tinka
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: New user: Intermediate tables and ForeignKey names

Postby massiws » Sun Apr 20, 2014 6:54 pm

Tinka,
when you click the magnifying glass of the lookup in the subform, the related table (in your case Bought) is opened and you can pick an existing record or add a new record (with the standard New Action Button) and then pick it to the subform.

Hope this helps,
Max
massiws
 
Posts: 503
Joined: Thu May 24, 2012 9:38 am
Location: Milan, Italy

Re: New user: Intermediate tables and ForeignKey names

Postby Tinka » Thu Jun 26, 2014 8:11 pm

Solved! Maybe not the easiest solution, but it works.
I have this structure:
table 1, table 2 intermediate table to n:n link table 1 to table 3, table 3

1. Edit form for table 1 is linked to intermediate table on general-sql
Code: Select all
Select * from [i]table_1 t1[/i]
Inner Join table_2 ON foreign_key = t1.primary_key


2. subform is based on intermediate table, has table_1 primary as foreign key and has this on Subform-SQL
Code: Select all
SELECT * FROM [i]intermediate_table [/i]
WHERE [i]foreign_key[/i] = '#RECORD_ID#'


3. New browse object in subform is a read-only lookup in table 3. Tab All: field = foreign_key 2 from the intermediate table Tab Lookup: ID-field: table 3 id code field: the desired db field (this is not the browse object in the tab Subform of the subform, which is the foreign_key 2 and has to be there)

4. button to add new subform objects calls edit form based on table 3 with default browse filter #RECORD_ID#
Tinka
 
Posts: 72
Joined: Mon Feb 24, 2014 11:28 pm

Re: New user: Intermediate tables and ForeignKey names

Postby admin » Fri Jun 27, 2014 10:20 am

.
admin
Site Admin
 
Posts: 3079
Joined: Mon Jun 15, 2009 9:53 am


Return to General