Deleting records from referenced tables.

[rnott]

I'm new to relational databases & nuBuilder...

How do you handle the problem that occurs when a user deletes records from referenced tables?

Using the nuBuilder invoice example... If a user was to delete a product, then all of the existing customer_product records that references that product_id would not know what the product code,name, etc. from that related product record.

Even if the user was to re-create the same product it would have a different product_id and therefore not show up on any existing invoices.

The only way I can think of to fix this problem is to get the database administrator to create a product with the same product_id value that was used on the existing customer_product records.
To prevent this problem, would you not allow anyone the ability to delete records from referenced tables?

[massiws]

rnott , you can manage these occurrences with PHP code in Before Save or After Delete tabs (see here)

[nuGent]

rnott, you are correct in your assumption that referenced items (eg items in lookup tables) must not be deleted (or created) by users otherwise havoc will ensue. Have a look into the concepts of 'referential integrity' and then apply appropriate permissions to save them from themselves. In a similar vein, once a unique product record has been created with a primary key, one should not change its key description.

Users often do silly things when adding new items in a parts list or a list of country codes etc:
1001: 15mm copper elbow
1002: 15mm coupling
...
1014: 15 mm coupling

It all makes work for the working man to do!

Cheers
John

[admin]

Australia has some weird postcodes eg.

Station Arcade 5000
Adelaide 5000
Adelaide 5001

In cases like this you need to rely on another field as the Primary Key.

In nuBuilder the PK is never seen by the user, relying on this, you should be fine.

Steven