Database design - system default items and custom user items

Question

This question applies to any database table design, where you would have system default items and custom user defaults of the same type (ie user can add his own custom items/settings).

Here is an example of invoicing and paymenttypes, By default an invoice can have payment terms of DueOnReceipt, NET10, NET15, NET30 (this is the default for all users!) therefore you would have two tables "INVOICE" and "PAYMENT_TERM"

INVOICE
Id
...
PaymentTermId



PAYMENT_TERM (System default)
Id
Name

Now what is the best way to allow a user to store their own custom "PaymentTerms" and why? (ie user can use system default payment terms OR user's own custom payment terms that he created/added)

Option 1) Add UserId to PaymentTerm, set userid for the user that has added the custom item and system default userid set to null.

INVOICE
Id
...
PaymentTermId



PaymentTerm
Id
Name
UserId (System Default, UserId=null)

Option 2) Add a flag to Invoice "IsPaymentTermCustom" and Create a custom table "PAYMENT_TERM_CUSTOM"

INVOICE
Id
...
PaymentTermId
PaymentTermCustomId
IsPaymentTermCustom (True for custom, otherwise false for system default)

PaymentTerm
Id
Name

PAYMENT_TERM_CUSTOM
Id
Name
UserId

Now check via SQL query if the user is using a custom payment term or not, if IsPaymentTermCustom=True, it means the user is using custom payment term otherwise its false.

Option 3) ????

...

Answer 1

As a general rule:

• Prefer adding columns to adding tables
• Prefer adding rows to adding columns

Generally speaking, the considerations are:

Effects of adding a table

• Requires the most changes to the app: You're supporting a new kind of "thing"
• Requires more complicated SQL: You'll have to join to it somehow
• May require changes to other tables to add a foreign key column referencing the new table
• Impacts performance because more I/O is needed to join to and read from the new table

Note that I am not saying "never add tables". Just know the costs.

Effects of adding a column

• Can be expensive to add a column if the table is large (can take hours for the ALTER TABLE ADD COLUMN to complete and during this time the table wil be locked, effectively bringing your site "down"), but this is a one-time thing
• The cost to the project is low: Easy to code/maintain
• Usually requires minimal changes to the app - it's a new aspect of a thing, rather than a new thing
• Will perform with negligible performance difference. Will not be measurably worse, but may be a lot faster depending on the situation (if having the new column avoids joining or expensive calculations).

Effects of adding rows

• Zero: If your data model can handle your new business idea by just adding more rows, that's the best option

(Pedants kindly refrain from making comments such as "there is no such thing as 'zero' impact", or "but there will still be more disk used for more rows" etc - I'm talking about material impact to the DB/project/code)

To answer the question: Option 1 is best (i.e. add a column to the payment option table).
The reasoning is based on the guidelines above and this situation is a good fit for those guidelines.

Further,
I would also store "standard" payment options in the same table, but with a NULL userid; that way you only have to add new payment options when you really have one, rather than for every customer even if they use a standard one.

It also means your invoice table does not need changing, which is a good thing - it means minimal impact to that part of your app.

Answer 2

It seems to me that there are merely "Payment Terms" and "Users". The decision of what are the "Default" payment terms is a business rule, and therefore would be best represented in the business layer of your application.

Assuming that you would like to have a set of pre-defined "default" payment terms present in your application from the start, these would already be present in the payment terms table. However, I would put a reference table in between USERS and PAYMENT TERMS:

USERS:
    user-id
    user_namde

USER_PAYMENT_TERMS:
    userID
    payment_term_id

PAYMENT_TERMS:
    payment_term_id
    payment_term

Your business layer should offer up to the user (or more likely, the administrator) through a GUI the ability to:

1. Assign 0 to many payment term options to a particular user (some users may not want one of the defaults to even be available, for example.
2. Add custom payment terms, which then become available for assignment to one or more users (but which avoids the creation of duplicate payment terms by different users)
3. Allows the definition of a custom payment term to be assigned to more than one user (say the user's company a unique payment process which requires all of their users to utilize a payment term other than one of the defaults? Create the custom term once, and assign to all users.

Your application business layer would establish rules governing access to payment terms, which could then be accessed by your user interface.

Your UI would then (again, likely through an administrator function) allow the set up of one or more payment terms in addition to the standards you describe, and then make them available to one or more users through something like a checked list box (for example).