Keeping track of "prices" in a SQLite database?

Question

My Schema looks like this:

CREATE TABLE items (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    quantity INTEGER NOT NULL
    price TEXT NOT NULL,
    category_id INTEGER NOT NULL
);
CREATE INDEX "item_id" ON "items" ("id");

I'm setting price as TEXT but it probably should be something different.

The data that I need to keep track of is $30/day. So there is a "cost" part of the number (in USD) and a "per something" part. How should I keep track of this data?

Answer 1

SQLite internally stores everything as text, so don't stress out about your type.

I'd change the column name to "price_per_day", and store it that way. If the item you're storing isn't priced "per day", normalize it to a day, then store it that way.

Answer 2

The price column should only be the monetary amount, which means the data type should be INTEGER. Then you need a column for the "per whatever" code, which would be a foreign key reference to a code table.

Use:

CREATE TABLE price_interval_code (
  code TEXT NOT NULL PRIMARY KEY,
  description TEXT NOT NULL,
);

CREATE TABLE items (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  quantity INTEGER NOT NULL
  price INTEGER NOT NULL,
  price_interval_code TEXT NOT NULL REFERENCES PRICE_INTERVAL_CODE(code),
  category_id INTEGER NOT NULL
);
CREATE INDEX "item_id" ON "items" ("id");