Multiple dates in one DB cell

Question

I have table in my DB for payments:

ID | Client_ID | Sale Date |  Total Payment | Paid | Paid Dates

And I want to make column Paid Dates saving type Date, but if client paid in several installments than there should be all this dates saved. Than table should have as many columns as dates plus first ones, but if there will be more than 20 installments than 20 columns? The easiest way I know is to concat these dates in String. And question: Is there any other way of saving multiple dates in one record cell?

Answer 1

The best way to do this is to create another table to save the dates and link it to your original table with a one-to-many relationship. Example:

Table1
ID | Client_ID | Sale Date | Total Payment | Paid

Table2
Table1_ID | Paid Date

This allows you to save as few and as many dates as you want, because they will be records rather than cells or concatenated values in one cell.

Answer 2

You need a little more design in your tables. The first issue with your idea (multiple dates on a single cell) is that you'll surely be asked the amount that was paid on each installment. In fact, as soon as this word "installment" entered the problem description, it's a sign that you need to represent that too.

Fortunately, that's exactly what relational databases are designed to do, and why there's a thing called database normalization.

In this case, it's quite simple: there should be another table for installments, with at least the date and amount, and a key to relate to the original table, which shouldn't have 'date' and 'paid' fields anymore.

sales table:

ID | Client_ID | Sale Date | Total Payment

installments table:

ID | Sale_ID | Date | Amount

to get all the dates and amounts for a given sale entry:

SELECT Date, Amount FROM Installment WHERE Sale_ID=xxx

to get the amount already paid:

SELECT Sale.ID, Sale.Client_ID, Sale.Sale_Date, -- other 'sale' fields
    SUM(Installment.Amount)
FROM Sale
    LEFT JOIN Installment ON (Installment.Sale_ID=Sale.ID)
WHERE Sale.XXXXX   -- any criteria for selecting the sale record

to get what a client owes you:

SELECT SUM(Sale.Total_Payment) - SUM(Installment.Amount)
FROM Sale
    LEFT JOIN Installment ON (Installment.Sale_ID=Sale.ID)
WHERE Sale.Client_ID=xxxx

Answer 3

The Answer by Javier and Answer by Racil Hilan are both correct. Cramming multiple values into a single field is the wrong approach unless you are absolutely 100% beyond-any-doubt certain that those values will only be used as a whole by the app and never searched or otherwise accessed by subcomponent values.

Naming

I would use another name other than Total Payment since this total is not in fact necessarily paid yet. Perhaps Cost. Good naming helps your code to be self-documenting.

Tips For SQL Names

For maximum portability across databases, and to avoid problems, name your columns with all lowercase letters, eliminate SPACE character, and use LOW LINE (underscore) between words.

And add a trailing LOW LINE (underscore). The SQL spec explicitly promises to never use a trailing underscore in any identifier. That means you will never have to worry about a collision between your name and any of the thousand-odd reserved words and keywords used by various databases. See this Answer by me.

So client_id_, date_of_sale_, and cost_.

Diagram

Here is a ERD diagram, similar to their suggested table design. I am using Postgres data types.

• Each client can have zero, one, or more sale records.
• Each sale record can have zero, one, or more payments.
• Each sale record must have exactly one assigned parent.
• Each payment record must have exactly one assigned parent.