I am just trying to teach myself SQL so appreciate any help or advice on this one.

I have a table for suppliers which has an attribute for payment method. The payment method includes but is not limited to a number of items. e.g. paypal, visa. I was thinking of having a small table for payment method containing valid options but I would like to update the table with information entered in the suppliers table if the payment method doesn't exists. How would I go about doing this?

I am using SQLite.



You have a few options.

You can handle this programmatically, in the code that sends the supplier info to the database.

You can create a stored procedure that finds payment methods in the supplier info that do not exist in the payment method table, and inserts them.

You can create a BEFORE trigger on supplier info, check if the payment method exists, and add it.

That said, I would advise against automatically creating something as critical as payment methods based on user input (or even on imported files).

First, the supplier info presumably will not have more than the payment method name. You may create a record, but critical info like payment type (credit card vs. checking account mean different data that you need to collect) will be missing.

Second, something as simple as a typo will create a new, invalid payment method in your system, instead of properly forcing the invalid data to be corrected before being added to the database.

Third, you would be allowing potential maliciously bad data to be created in your system. Without proper controls, you might allow someone to actually place valid fillable orders, and charge them nothing.

Screens that allow data to be added to your core look-up tables should be tightly controlled, only accessible to users who know what to add, and how.

| improve this answer | |
  • A Before trigger looks like a good way to do this. However, good point about potential incorrect data due to typos. – Urban Gemz May 29 '17 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.