You will get many answers to this I am sure, many idealist answers too, I can only answer from my experience with financials and what actually goes on.
You have already covered the majority of issues.
Rounding precision tends to not actually be much of an issue in my experience. The majority of large financial organisations that have not grown up overnight (i.e. everything except hedge funds) have a huge range of legacy applications that are split up due to various fuels. They tend to not do rounding precision consistently; generally a certain error profit and loss is simply accepted for rounding. Indeed many man hours are spent in places I've worked where humans where the ultimate 'yes that is close enough' selectors when it comes to matching exact/expected sums. Remember, this is an answer based on reality, not what should happen.
Encryption - don't rely on it frankly. Store indentifying data in a physically and logically separate system than de-identified data (i.e. account code everywhere, personal data separate).
Generally while backups are required, offline backups are rarely called on - things have gone badly wrong at that point. Warm production copies are generally required - however this will depend on your own specific needs. In general practise we have a warm onsite production copy of all systems AND a disaster recovery site with its own production and warm copies. Warm copies tend to be a few minutes behind in replication etc.
Auditing is the key to every financial system I’ve ever worked on. You have 2 fundamental requirements
A) Can you track every single change made to the data, by whom, when and why?
B) Can you prove the historical state of your data? That it hasn’t been tampered with?
A) is required for operations teams – your system will be used in 100 ways you never expected, and this information is vital for expansion, ad-hoc reporting, legal reasons and debugging.
B) See the AMEX vs. Vee Vinhnee case – where AMEX were unable to collect 40k owed to them as they could not prove that their records were not made up. The solution generally used for this is trusted time stamping. Large financials have guarantor banks that guarantee transactions and thus inherently provide trusted time stamping. There are commercial providers for this for other walks of lives/scenarios.
Ultimately, it comes down to use and architecture.
Architecture
Does the system handle "any sport"? Is the idea that you put on your architecture astronaut hat, and build a generic system that can handle any future type of sport that may not even exist today?
If so, obviously having dynamically-named tables is a huge pain, so it would make sense to have a schema that supports n sports, if required.
That said, I have a very strong bias against this approach: this is almost always more work and leads to poorer results. Making a separate UI, schema, etc, for each sport will ultimately lead to better user experience and easier to maintain code, even if it does mean some superficial amount of duplication (how to avoid/minimize this is a separate question).
How do you handle players that play multiple sports? Do they get two entries (eg, you treat as different people) or are you trying to do something specific with them?
Use
So let's assume you don't do sports dynamically (eg, if someone wants to add a new sport, it requires development effort to add it).
Is there ever a time where you're displaying players (or any other object you mentioned) from more than one sport at a time?
I could see this for a search function, where you could search by player or team name (regardless of sport), but beyond that I can't imagine many use cases.
If you never need to do this, then your approach is perfectly fine. You can stop reading here.
Alternative Schemas
Views
I'm a fan of KISS. In 15+ years of software development, I continue to fall back to the philosophy "build the simplest thing that works".
So my initial reaction, assuming a cross-sport search function is really the only use case, is to create views:
SELECT PlayerName, 'NFL' as [Sport], TeamName FROM NFL_Players JOIN NFL_Teams ...
UNION
SELECT PlayerName, 'NHL' as [Sport], TeamName FROM NHL_Players JOIN NHL_Teams ...
UNION ....
Of course, if you add a new sport, you have to add to the view. It may also be useful to include other common information but really that's dependent on what needs to get shown.
I'd try to keep all the sport-specific stuff in the View definition, so the search code doesn't need to have much or any specific code (besides maybe knowing how to link to /nhl/players/player-name
vs /nfl/...
or however your app does that).
Table Inheritence
Table inheritance can work, but is pretty complex. I don't have a ton of experience with it, and in fact, I think every time I've been involved in evaluating it we ended up doing something simpler (like I'm suggesting here).
So personally, I have yet to find why this would be useful, but maybe there is a convincing use case (that I don't know of) that justifies the complexity (eg, Table inheritance solves the use case better than any other solution).
Separate tables for sport-specific attributes
You could do a single players
table which has attributes common to all players of all sports, and then another set of tables like nhl_players_details
that contains a playerId and columns with additional info on the player. If there are a ton of common attributes, or you have many uses of "all players from all sports" then this may make sense.
Key value pairs for sport-specific attributes
Completely alternative approach: have a players
table (again, with common attributes like name) and then a player_data
table that has PlayerId
, Sport
, Attribute
, Value
. The attribute names entered would be sport-specific. This allows you to essentially add new attributes without modifying the schema (your code would still have to know to load/display them of course). The drawback is you lose some integrity: value would typically be a string field, so your app code would have to be resilient and handle potential failures converting the string value
to a specific data type (like integer).
This concept can of course apply to Teams, Games, etc.
Best Answer
No, they're not. The purpose of auditing is to determine who did what in the past. The current data is irrelevant for this purpose, but you will need to record which data changed - there's no point in saying "user Dave change the person table" without saying what he changed. This information doesn't have to be recorded so formally though.
You might like to store the table that was modified, and a text field containing the data changes to assist in filtering the audit records during an investigation, or just the data changes if you can filter on other factors or substring searches of the text.