Trainwreck Database

Rattail defines a basic / common schema for POS Transactions, for what it calls a "Trainwreck" database. (This is in the rattail.trainwreck.db.model module.) Again you're free to extend this schema, in fact Rattail defines it in such a way that you "must" extend it technically, even if you add nothing to it. The idea is that this Trainwreck DB would be robust enough to store all "pertinent" info about transactions, in a POS-neutral way. This has a few advantages, namely:

• Trainwreck DB schema is often "simpler" than actual POS schema, but it should suit your needs (and if not, extend)
• once data is imported to Trainwreck, any reporting done against Trainwreck is also then simpler
• can also record "other" data in Trainwreck for things (e.g. "member status") which POS doesn't know about

  • sometimes POS does know, but doesn't record this info within the transaction, but we can assume "current value"

• sometimes POS data is not in a DB (e.g. flat file), so import to Trainwreck makes SQL reporting possible
• the more your reports use Trainwreck, the more easily you can migrate to different POS

  • er, must write new POS -> Trainwreck importer, but then reports would ideally need no changes

• Trainwreck data can also be exposed via Poser (Tailbone) web app

The schema for Trainwreck which is defined by Rattail, only includes transaction "header" and "detail" (line item) tables, at this time. You're free to add columns to either, as well as additional tables. You are expected to "extend" the web app views also, however minimally.

There is some basic support for "sharding" in a sense. Ideally you would have one DB for "current" transactions (going back how far, is up to you), plus a separate DB for each "year" of transactions. These two "types" of DB's need not be exclusive, meaning if today is Jan 1, 2019 then we may have a separate 2018 DB but our "current" DB might still also include all of 2018's transactions. So when you split off a year (or just a day) at a time from "current" DB would be up to you. The web app supports exposing a "DB picker" so each user can locate "current" or "archived" transactions.

It's worth noting some "cons" for this Trainwreck approach:

• it can be difficult (but usually not impossible) to get the data importing 100% accurate

  • e.g. sometimes what you think the data "should be" will even disagree with your POS

  • but it's sometimes tedious to find the transaction [item] responsible for diffs between Trainwreck and POS
• sometimes you decide to extend Trainwreck schema further, and then must "back-fill" historical transactions
  • at least, to the extent needed - this can take quite a while depending on size of archive etc.
  • or, sometimes you must back-fill b/c you discover a mistake in the import logic, so must fix

Pros outweigh the cons here though, IMHO.