SQLite – How to Split a Column into New Columns

There are a lot of places internally where it is possible to reference data using a user id, which is better than a string. For example, if you had 4 tables referencing the user table, they'd each have a string column which is less efficient than 4 integer indexed columns. This is the reason why most schemas use this layout; you're more likely to want to reference a user than to refer to the user by name.

However, you can also set username to be an index, which you might want to do if you are looking it up a lot.

To answer your edited question, a 24 character text field will require roughly 24 bytes. Depending on whether it's a varchar or a char it will be stored differently, but the full amount will be needed for searching, so let's consider them both to be 24.

An integer field will require 4 bytes.

You can see how this will add up over time. For comparisons, assume a linear time comparison, meaning 24 operations to compare two a string field, while only 4 operations to compare an integer field. In practice, they will probably take roughly the same amount of time, though the string comparison cannot be faster.

If you want to search by username, create a table with the schema of username | user_id. That'll let you search by username and join with the other data you are looking for while still being efficient.

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.