SQL table definition best practice for column-CodePudding

I have these fees prices depending on monthly transactions Source A:

| Monthly Transactions | Price / Transaction |
| -------------------- | ------------------- |
| 1 - 5,000            | $2.50               |
| 5,001 - 7,500        | $2.40               |
| 7,501 - 10,000       | $2.30               |
| 10,001 and above     | $2.20               |

Source B:

| Monthly Transactions | Price / Transaction |
| -------------------- | ------------------- |
| 1 - 5,000            | $2.00               |
| 5,001 - 7,500        | $1.50               |
| 7,501 - 10,000       | $1.00               |
| 10,001 and above     | $0.50               |

With the data presented above and I want to create the following table:

| Source_name | from_transaction | to_transaction | fee  |
| ---------   | --------------   | -------------- | ---- |
| Source A    | 1                | 5000           | 2.50 | 
| Source A    | 5001             | 7500           | 2.40 |  
| Source A    | 7501             | 10000          | 2.30 | 
| Source A    | 10000            | 'and above'    | 2.20 | 
| Source B    | 1                | 5000           | 2.00 | 
| Source B    | 5001             | 7500           | 1.50 |  
| Source B    | 7501             | 10000          | 1.00 | 
| Source B    | 10000            | 'and above'    | 0.50 |

What value should I put in to_transaction column instead 'and above'? What would be the best practice?

I'll later do an inner join with another table using a BETWEEN statement

INNER JOIN fees f ON f.Source_name = t.Source_name AND t.transaction_count BETWEEN f.from_transaction AND f.to_transaction;

CodePudding user response：

If you are using money as the type for to_transaction you can use 92233720368547758.

https://www.postgresql.org/docs/current/datatype-money.html

Oher option could be to use -1 for to_transaction and when that is the case just check for t.transaction_count > f.to_transaction so your query has to change a bit

INNER JOIN fees f ON f.Source_name = t.Source_name 
AND ((f.to_transaction = -1) && (t.transaction_count > f.from_transaction))
OR (t.transaction_count BETWEEN f.from_transaction AND f.to_transaction;

CodePudding user response：

prefect use case for range data type.

CREATE TABLE txn_range_price_ref (
    source text,
    txn_qty_range int8range,
    fee numeric
);

INSERT INTO txn_range_price_ref
    VALUES ('Source A', '[1, 5000)'::int8range, 2.50),
    ('Source A', '[5001,7500)'::int8range, 2.40),
    ('Source A', '[7501,10000)'::int8range, 2.30),
    ('Source A', '[10000,)'::int8range, 2.20),
    ('Source B', '[1, 5000)'::int8range, 2.00),
    ('Source B', '[5001,7500)'::int8range, 1.50),
    ('Source A', '[7501,10000)'::int8range, 1.00),
    ('Source A', '[10000,)'::int8range, 0.50);

then You can do something like:

INNER JOIN txn_range_price_ref f ON f.Source_name = t.Source_name
    AND t.transaction_count <@ f.txn_qty_range

<@ operator: https://www.postgresql.org/docs/current/functions-range.html
range: https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-INFINITE

be careful with infinity with empty white spaces. because select '[10000,)'::int8range; is ok, but select '[10000, )'::int8range; will fail.