Database Design — System Defaults and User Custom Elements

Question

This question applies to any database table design where you will have system default elements and user default settings of the same type (i.e. the user can add his own elements / settings).

Here is an example of billing and payment types. By default, the invoice may have payment conditions DueOnReceipt, NET10, NET15, NET30 (this is the default value for all users!), So you will have two tables "INVOICE" and "PAYMENT_TERM" "

INVOICE
Id
...
PaymentTermId



PAYMENT_TERM (System default)
Id
Name

Now, what is the best way to let the user store their own "PaymentTerms" and why? (i.e. the user can use the default payment terms for the system or the custom user payment terms that he created / added)

Option 1) Add the UserId to PaymentTerm, set the userid for the user who added the user element and the system default user identifier to null.

INVOICE
Id
...
PaymentTermId



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

Option 2) Add a flag to the 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 with the SQL query if the user uses a custom payment term or not, if IsPaymentTermCustom = True, this means that the user uses a custom payment term, otherwise its value is false.

Option 3)

...

Answer 1

:

, :

• : ""
• SQL: -
• , .
• , - .

, " ". .

• , , ( ALTER TABLE ADD COLUMN , , "" ),
• : /
• - , .
• . , ( ).

• Zero: -, ,

( , " , " "" " , " ​​ .. - DB//)

: 1 (.. ).
, .

,
"" , NULL userid; , , , .

, , - .

Answer 2

1 : -

• Invoice to PaymentTerm

• , Invoice to PaymentTerm, . , , , . , , , .
• PaymentTerms,

1 .

Answer 3

, " " "". , , - - .

, "" , , . :

USERS:
    user-id
    user_namde

USER_PAYMENT_TERMS:
    userID
    payment_term_id

PAYMENT_TERMS:
    payment_term_id
    payment_term

(, , ) GUI :

• 0 ( , .
• , ( ).
• (, , , ? .

- , , .

( , , ) , - ().

Answer 4

, , , . - , "2% 10 NET 30", 2%, 10 , - 30 ".

, , . , ( ) "", , user_id 0.

create table payment_terms (
  payment_term_id integer primary key,
  payment_term_owner_id integer not null references users (user_id),
  payment_term_desc varchar(30) not null unique,
);

insert into payment_terms values (1, 0, 'Net 10');
insert into payment_terms values (2, 0, 'Net 15');
...
insert into payment_terms values (5, 1, '2% 10, Net 30');

.

. , , , . ( : .) , , .

, , 5, "2% 10, Net 30". 5 -. , -, "2% 10, Net 20". .

. .

, , . .

• DELETE.
• , .
• , .

( - .)

Answer 5

:

.

:

• Things get straight for db