Volz Volz - 3 months ago 14
SQL Question

What data type use for taxrate field

What data type should I use for tax rate field in my RoR application. I want it to store only numbers from 0 to 100 and two fixed strings "zw" and "np". Should I use

string
type and parse it to integer when it's number?

Answer

I think, perhaps, you should think about the tax rate as a name of the rate rather than a numeric value.

Create a reference table of tax rates, something like:

create table TaxRates (
    TaxRateId int primary key, -- auto_increment/serial/identity
    Name varchar(255) not null unique,
    Value int, -- NULL if not appropriate
    <more columns if necessary>
);

The drop-down list can use this table for the names. Most of the names will be numbers, but that is ok. The actual numeric value will be in the Value field (which you might really want to be a decimal).

Any table that uses a tax rate would have a foreign key reference to TaxRateId.