should I create another table for 0 or 1 entries?

Question

Hello,

I have a payment_types table in which you can enter different payment types such as direct debit, credit card, credit card on file, etc. for an event. For each payment type, you can specify whether to allow user to pay by installment and what the start and end date and number of installments it should allow. Should I have a separate Installments table that links to payment_types since you can have a payment without installments? Or should I just have those installment columns be blank in the payment_types table in case it doesn't allow installments?

edit: the start and end date mentioned above apply to installment and not the payment type itself. payment type itself won't have any date ranges since the event itself will have a date range.

Answer 1

Since the installation information (whether allowed and dates) are tied to the payment type, I would put them in the same table. Also, I would not use a separate flag for whether or not installed are allowed. If the start and end date is NULL, then installments are not allowed. If you use a separate field, you could end up with bad date (i.e. installment flag is 0, but the dates are non-null)

If a payment can have more than one date range, then a separate table makes sense, but since the dates are directly related to the payment type, keep them in the same table.

Answer 2

Put the installment information into the payment type table if the payments cannot have more than 1 type of date range. If there is more than one date range type (e.g. once a month, over 6 months). If it can then put it into it's own table. If you do put it into its own table then you will just have 2 FKs from your main table, 1 to your payment type and 1 to your payment schedule. If the payment schedule FK is null then you would know it is a 1 time payment. That means you shouldn't duplicate your flag showing if the payment has a schedule or not in the main payment table. That will reduce duplication and errors.