[web2py] Re: Postgres expers -- interesting design issue
Cliff
By adding the 'null' to the 'purchase_order_id' in the product_lots table,
you automatically achieve it - that is, if the field is declared as Null,
foreign key constraint is applicable only if it is non-null and a null
value will automatically skip validation for foreign key.
Another method (not really required, I guess) is to define a 'dummy' PO
with id as zero (or non-negative) and refer to this PO in all product lots
which do not require any PO reference. However, this method will mean that
you have to have an additional where clause in all selects (on the PO
table) to exclude this specific PO.
Cheers.
Sundar
==
On Friday, April 20, 2012 3:16:24 AM UTC+5:30, Cliff wrote:
nick,
Thanks for the response.
Here's a longer explanation.
Two applications, one is purchasing, the other is production.
Purchasing has two tables, purchase_orders and product_lots. It's a
one-to-may relationship; a purchase order can have many product lots. So
we get:
db.define_table('purchase_orders', field('po_number' .
db.define_table('product_lots', Field('purchase_order_id',
db.purchase_orders, requires=IS_IN_DB(db, 'purchase_orders.id', ...)),
Field('quantity_received', 'decimal'), Field('quantity_on_hand',
'decimal') ...
So, I have established a foreign table relationship between purchase
orders and product lots.
Once a product_lot is released to production, the production app uses the
same table to find available raw materials, and decrements the
quantity_on_hand field as it goes. The production app neither knows nor
cares about the purchase order table, so the purchase_order_id field is
irrelevant.
This is fine as long as all production material enters the plant via the
purchasing process. However, this is not the case. So, either I devise a
way to get records into the product lots table without violating the
foreign key constraint that Postgres would apply. One way would be to
define the purchase_order_id field as an integer and handle the key
relationships through code. Not attractive.
Another way would be to establish a third table for raw materials. This
would be a clone of the product_lots table but it would not have a foreign
key to purchase_orders. The purchase_order controller would then need to
create and update records in the raw materials table, while several
controllers in the production app would also create and update records in
the table as well. I don't like this solution much, either.
Can anybody suggest another approach?
On Thursday, April 19, 2012 1:03:41 PM UTC-4, nick name wrote:
I don't understand what you are trying to achieve, but whatever it is,
you are doing it wrong; your model should be:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a', 'reference
A'))
# alternatively:
# db.define_table('B', Field('name'), Field('id_from_table_a', db.A))
This would create the foreign key reference, and would allow nulls by
default (which is what your condition does).
But as I said, I'm not sure exactly what you are trying to achieve?
On Wednesday, April 18, 2012 7:11:42 PM UTC-4, Cliff wrote:
There are two tables, as follows:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a))
Also there are two applications. One, called 'both_a_and_b', uses both
tables and uses 'table_a_id' as a foreign key in table B. The other,
called 'table_b_only' needs CRUD access to the information in table B, but
it is not able to supply a value for 'id_from_table_a.'
I think because Postgres recognizes foreign key constraints,
'table_b_only' will not be able to create records in table B.
What Is the right solution?
I can think of two. First, create a third table, C, for all the data
that 'table_b_only' needs. This table would not have the 'id_from_table_a'
field. The other application would need to write also to this table
whenever it creates a record in table B.
A second possibility might be to define table B this way:
db.define_table('B',
Field('name') ,
Field('id_from_table_a', requires= IS_EMPTY_OR(IS_IN_DB(db, 'A.id',
...)))
)
I would be grateful for any guidance,
Cliff Kachinske
[web2py] Re: Postgres expers -- interesting design issue
Thank you, Sundar.
I understand. This solution means I relinquish the foreign key constraint
on the product_lots table.
It does seem simpler than using another table for raw materials.
No third alternative, I guess...
On Friday, April 20, 2012 2:37:38 AM UTC-4, Sundar wrote:
Cliff
By adding the 'null' to the 'purchase_order_id' in the product_lots table,
you automatically achieve it - that is, if the field is declared as Null,
foreign key constraint is applicable only if it is non-null and a null
value will automatically skip validation for foreign key.
Another method (not really required, I guess) is to define a 'dummy' PO
with id as zero (or non-negative) and refer to this PO in all product lots
which do not require any PO reference. However, this method will mean that
you have to have an additional where clause in all selects (on the PO
table) to exclude this specific PO.
Cheers.
Sundar
==
On Friday, April 20, 2012 3:16:24 AM UTC+5:30, Cliff wrote:
nick,
Thanks for the response.
Here's a longer explanation.
Two applications, one is purchasing, the other is production.
Purchasing has two tables, purchase_orders and product_lots. It's a
one-to-may relationship; a purchase order can have many product lots. So
we get:
db.define_table('purchase_orders', field('po_number' .
db.define_table('product_lots', Field('purchase_order_id',
db.purchase_orders, requires=IS_IN_DB(db, 'purchase_orders.id', ...)),
Field('quantity_received', 'decimal'), Field('quantity_on_hand',
'decimal') ...
So, I have established a foreign table relationship between purchase
orders and product lots.
Once a product_lot is released to production, the production app uses the
same table to find available raw materials, and decrements the
quantity_on_hand field as it goes. The production app neither knows nor
cares about the purchase order table, so the purchase_order_id field is
irrelevant.
This is fine as long as all production material enters the plant via the
purchasing process. However, this is not the case. So, either I devise a
way to get records into the product lots table without violating the
foreign key constraint that Postgres would apply. One way would be to
define the purchase_order_id field as an integer and handle the key
relationships through code. Not attractive.
Another way would be to establish a third table for raw materials. This
would be a clone of the product_lots table but it would not have a foreign
key to purchase_orders. The purchase_order controller would then need to
create and update records in the raw materials table, while several
controllers in the production app would also create and update records in
the table as well. I don't like this solution much, either.
Can anybody suggest another approach?
On Thursday, April 19, 2012 1:03:41 PM UTC-4, nick name wrote:
I don't understand what you are trying to achieve, but whatever it is,
you are doing it wrong; your model should be:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a', 'reference
A'))
# alternatively:
# db.define_table('B', Field('name'), Field('id_from_table_a', db.A))
This would create the foreign key reference, and would allow nulls by
default (which is what your condition does).
But as I said, I'm not sure exactly what you are trying to achieve?
On Wednesday, April 18, 2012 7:11:42 PM UTC-4, Cliff wrote:
There are two tables, as follows:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a))
Also there are two applications. One, called 'both_a_and_b', uses both
tables and uses 'table_a_id' as a foreign key in table B. The other,
called 'table_b_only' needs CRUD access to the information in table B, but
it is not able to supply a value for 'id_from_table_a.'
I think because Postgres recognizes foreign key constraints,
'table_b_only' will not be able to create records in table B.
What Is the right solution?
I can think of two. First, create a third table, C, for all the data
that 'table_b_only' needs. This table would not have the
'id_from_table_a'
field. The other application would need to write also to this table
whenever it creates a record in table B.
A second possibility might be to define table B this way:
db.define_table('B',
Field('name') ,
Field('id_from_table_a', requires= IS_EMPTY_OR(IS_IN_DB(db, 'A.id',
...)))
)
I would be grateful for any guidance,
Cliff Kachinske
[web2py] Re: Postgres expers -- interesting design issue
Not sure if 'relinquish' is the right word since the foreign constraint is
going to be enforced if you enter a value for the PO. (May be my statement
was a little confusing).
Please see the example:
Create Table t1(a int primary key)
Create table t2 (b int primary key, ba null references t1(a))
I just wanted to make sure of this point.
Sundar
=
On Friday, April 20, 2012 6:03:47 PM UTC+5:30, Cliff wrote:
Thank you, Sundar.
I understand. This solution means I relinquish the foreign key constraint
on the product_lots table.
It does seem simpler than using another table for raw materials.
No third alternative, I guess...
On Friday, April 20, 2012 2:37:38 AM UTC-4, Sundar wrote:
Cliff
By adding the 'null' to the 'purchase_order_id' in the product_lots
table, you automatically achieve it - that is, if the field is declared as
Null, foreign key constraint is applicable only if it is non-null and a
null value will automatically skip validation for foreign key.
Another method (not really required, I guess) is to define a 'dummy' PO
with id as zero (or non-negative) and refer to this PO in all product lots
which do not require any PO reference. However, this method will mean that
you have to have an additional where clause in all selects (on the PO
table) to exclude this specific PO.
Cheers.
Sundar
==
On Friday, April 20, 2012 3:16:24 AM UTC+5:30, Cliff wrote:
nick,
Thanks for the response.
Here's a longer explanation.
Two applications, one is purchasing, the other is production.
Purchasing has two tables, purchase_orders and product_lots. It's a
one-to-may relationship; a purchase order can have many product lots. So
we get:
db.define_table('purchase_orders', field('po_number' .
db.define_table('product_lots', Field('purchase_order_id',
db.purchase_orders, requires=IS_IN_DB(db, 'purchase_orders.id', ...)),
Field('quantity_received', 'decimal'), Field('quantity_on_hand',
'decimal') ...
So, I have established a foreign table relationship between purchase
orders and product lots.
Once a product_lot is released to production, the production app uses
the same table to find available raw materials, and decrements the
quantity_on_hand field as it goes. The production app neither knows nor
cares about the purchase order table, so the purchase_order_id field is
irrelevant.
This is fine as long as all production material enters the plant via the
purchasing process. However, this is not the case. So, either I devise a
way to get records into the product lots table without violating the
foreign key constraint that Postgres would apply. One way would be to
define the purchase_order_id field as an integer and handle the key
relationships through code. Not attractive.
Another way would be to establish a third table for raw materials. This
would be a clone of the product_lots table but it would not have a foreign
key to purchase_orders. The purchase_order controller would then need to
create and update records in the raw materials table, while several
controllers in the production app would also create and update records in
the table as well. I don't like this solution much, either.
Can anybody suggest another approach?
On Thursday, April 19, 2012 1:03:41 PM UTC-4, nick name wrote:
I don't understand what you are trying to achieve, but whatever it is,
you are doing it wrong; your model should be:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a', 'reference
A'))
# alternatively:
# db.define_table('B', Field('name'), Field('id_from_table_a', db.A))
This would create the foreign key reference, and would allow nulls by
default (which is what your condition does).
But as I said, I'm not sure exactly what you are trying to achieve?
On Wednesday, April 18, 2012 7:11:42 PM UTC-4, Cliff wrote:
There are two tables, as follows:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a))
Also there are two applications. One, called 'both_a_and_b', uses
both tables and uses 'table_a_id' as a foreign key in table B. The
other,
called 'table_b_only' needs CRUD access to the information in table B,
but
it is not able to supply a value for 'id_from_table_a.'
I think because Postgres recognizes foreign key constraints,
'table_b_only' will not be able to create records in table B.
What Is the right solution?
I can think of two. First, create a third table, C, for all the data
that 'table_b_only' needs. This table would not have the
'id_from_table_a'
field. The other application would need to write also to this table
whenever it creates a record in table B.
A second possibility might be to define table B this way:
db.define_table('B',
Field('name') ,
[web2py] Re: Postgres expers -- interesting design issue
I don't understand what you are trying to achieve, but whatever it is, you
are doing it wrong; your model should be:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a', 'reference A'))
# alternatively:
# db.define_table('B', Field('name'), Field('id_from_table_a', db.A))
This would create the foreign key reference, and would allow nulls by
default (which is what your condition does).
But as I said, I'm not sure exactly what you are trying to achieve?
On Wednesday, April 18, 2012 7:11:42 PM UTC-4, Cliff wrote:
There are two tables, as follows:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a))
Also there are two applications. One, called 'both_a_and_b', uses both
tables and uses 'table_a_id' as a foreign key in table B. The other,
called 'table_b_only' needs CRUD access to the information in table B, but
it is not able to supply a value for 'id_from_table_a.'
I think because Postgres recognizes foreign key constraints,
'table_b_only' will not be able to create records in table B.
What Is the right solution?
I can think of two. First, create a third table, C, for all the data that
'table_b_only' needs. This table would not have the 'id_from_table_a'
field. The other application would need to write also to this table
whenever it creates a record in table B.
A second possibility might be to define table B this way:
db.define_table('B',
Field('name') ,
Field('id_from_table_a', requires= IS_EMPTY_OR(IS_IN_DB(db, 'A.id',
...)))
)
I would be grateful for any guidance,
Cliff Kachinske
[web2py] Re: Postgres expers -- interesting design issue
nick,
Thanks for the response.
Here's a longer explanation.
Two applications, one is purchasing, the other is production.
Purchasing has two tables, purchase_orders and product_lots. It's a
one-to-may relationship; a purchase order can have many product lots. So
we get:
db.define_table('purchase_orders', field('po_number' .
db.define_table('product_lots', Field('purchase_order_id',
db.purchase_orders, requires=IS_IN_DB(db, 'purchase_orders.id', ...)),
Field('quantity_received', 'decimal'), Field('quantity_on_hand',
'decimal') ...
So, I have established a foreign table relationship between purchase orders
and product lots.
Once a product_lot is released to production, the production app uses the
same table to find available raw materials, and decrements the
quantity_on_hand field as it goes. The production app neither knows nor
cares about the purchase order table, so the purchase_order_id field is
irrelevant.
This is fine as long as all production material enters the plant via the
purchasing process. However, this is not the case. So, either I devise a
way to get records into the product lots table without violating the
foreign key constraint that Postgres would apply. One way would be to
define the purchase_order_id field as an integer and handle the key
relationships through code. Not attractive.
Another way would be to establish a third table for raw materials. This
would be a clone of the product_lots table but it would not have a foreign
key to purchase_orders. The purchase_order controller would then need to
create and update records in the raw materials table, while several
controllers in the production app would also create and update records in
the table as well. I don't like this solution much, either.
Can anybody suggest another approach?
On Thursday, April 19, 2012 1:03:41 PM UTC-4, nick name wrote:
I don't understand what you are trying to achieve, but whatever it is, you
are doing it wrong; your model should be:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a', 'reference
A'))
# alternatively:
# db.define_table('B', Field('name'), Field('id_from_table_a', db.A))
This would create the foreign key reference, and would allow nulls by
default (which is what your condition does).
But as I said, I'm not sure exactly what you are trying to achieve?
On Wednesday, April 18, 2012 7:11:42 PM UTC-4, Cliff wrote:
There are two tables, as follows:
db.define_table('A', Field('name'))
db.define_table('B', Field('name'), Field('id_from_table_a))
Also there are two applications. One, called 'both_a_and_b', uses both
tables and uses 'table_a_id' as a foreign key in table B. The other,
called 'table_b_only' needs CRUD access to the information in table B, but
it is not able to supply a value for 'id_from_table_a.'
I think because Postgres recognizes foreign key constraints,
'table_b_only' will not be able to create records in table B.
What Is the right solution?
I can think of two. First, create a third table, C, for all the data
that 'table_b_only' needs. This table would not have the 'id_from_table_a'
field. The other application would need to write also to this table
whenever it creates a record in table B.
A second possibility might be to define table B this way:
db.define_table('B',
Field('name') ,
Field('id_from_table_a', requires= IS_EMPTY_OR(IS_IN_DB(db, 'A.id',
...)))
)
I would be grateful for any guidance,
Cliff Kachinske
