New user: Intermediate tables and ForeignKey names
Posted: Tue Nov 22, 2011 12:58 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: http://forums.nubuilder.cloud/viewtopic.p ... w+user#p64
[EDIT: Corrected the statement about the ForeignKey entry in Subforms.]
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: http://forums.nubuilder.cloud/viewtopic.p ... w+user#p64
[EDIT: Corrected the statement about the ForeignKey entry in Subforms.]