Databases in Finance: Where Structured Data Sits
A database is a store where a table's shape is declared before any row enters: a name and a type for every column, and a key that says what one row is. Declare the Neelbagh returns on the stall and the month and the store accepts 31 rows of 32 and names the one it turns away.
Somebody hands an analyst a table and says the data is in the database now. The sentence sounds like a statement about a location, as though the rows had been moved from one shelf to another and are otherwise unchanged. A database is not a location at all. Something happened to those rows on the way in. Every row was held up against a shape that somebody wrote down first, and any row that did not match the shape did not get in. What that shape is, who writes it, and what it can and cannot see are the three things worth knowing about any store.
Every figure below is a count of rows, and every count was worked out by offering the same 32 rows to a declared shape and seeing what came back. A count of rows carries no as of date, and nothing in it changes with the day it is read.
What is already settled
Three things arrive here finished, and none of them gets argued again.
- The Neelbagh stall record itself. An invented covered market, a market office that writes one line per stall per month across four months, 32 lines in eight columns, and the faults that record already carries. The faults were diagnosed in an earlier reading and are used here as material rather than discovered again.
- The habit of counting rows at every step. A store that refuses a row is worth nothing at all unless a human being reads the number of refusals. Reading that number is a discipline rather than a feature.
- Counting, and almost nothing else. Every figure below is a count of rows let in, a count of rows kept out, or one single average that exists only to show what a completely empty average looks like.
What is a database, and what does it declare?
The market office started with paper, so start there too. The clerk at Neelbagh used to keep a ruled ledger with the column headings printed at the head of every sheet: stall, licence, month, takings, pitch. The boxes were ruled and the headings were printed before a single figure was ever entered, and that ordering is the entire idea. There is only one box, so the clerk cannot write two takings figures for one stall in one month without visibly writing over something. The shape came first, and the shape is what refuses.
A database is that ledger with the ruling enforced by a machine. Before any row enters, three things are settled and written down. First, the columns and the order they sit in. Second, a declared type for each column. The type says what kind of value may sit in that column. Third, a key. The key states what one row of the table describes. The whole difference between a file and a store sits there: a file carries data and makes no promises about it, and a store refuses data that does not match the promises it was given.
The key on that form is easy to skim past. Notice its job. A key is not a setting for making lookups quicker. A key is a sentence about meaning: one row is one stall's return for one month. Once that sentence is written down, a second row claiming to be the same stall's return for the same month is not extra information, it is a contradiction, and the store is entitled to say so. The two columns named in the key are the two columns that carry that sentence, and every other column in the table is just something the row happens to also say.
A store settles three things before any row enters. Which set names all three?
What does a declared type do that a file does not?
A declared type says what kind of value a column is allowed to hold, and everything interesting about types happens in the one column where the answer is genuinely arguable. Seven of the Neelbagh columns are easy. A month is a whole number between 1 and 4. Takings are a whole number of rupees. A pitch is a whole number of square feet. A filing day is a whole number naming a day. Names and categories are text. Then there is licence_no, and licence_no is the one that decides whether the store is any help at all.
Before reading on, commit to an answer. A licence number can be declared as text or as a whole number. Which declaration makes a meaningless question impossible, rather than merely unwise?
A licence number is an identifierA label with no job except naming a single thing, so that two of them are never confused. Digits are merely the alphabet somebody chose for it, which is why arithmetic on one answers nothing.. Its entire job is to point at one stall. The number happens to be written with digits, an accident of how the office issues them, and the digits carry no quantity. Licence 2743 is not larger than licence 2104 in any sense a person could use, only different. Declare that column as text and the store will sort it, group by it and match on it, and will offer no way at all to add two of them together. Declare it as a whole number and the store will cheerfully do arithmetic on it.
Here is what that costs, on this record, exactly. The eight stalls still trading carry licences 2104, 2216, 2318, 2405, 2477, 2530, 2618 and 2743. Added, they come to 19,411. Divided by the eight stalls, that is 2426.375. The average of eight licence numbers is not money, not a size, not a rate and not anything at all, and nothing in the store objected to producing it. No warning appeared and no line went into any report. The answer came back to three decimal places, exactly the sort of precision that makes a person trust a number. A declared type is not paperwork. The type is how a meaningless question is made impossible to ask rather than merely unwise to ask.
The store returns 2426.375 for the average of the licence numbers and raises nothing. What went wrong, and where was it preventable?
What is a key, and what does one turn away?
A key is the declaration of the grainThe sentence saying what one line of a table stands for. Until somebody writes that sentence down, every count taken off the table is a guess about what has just been counted.. The declaration is one sentence, and the sentence takes the form: one row of this table is one of these. One row is one stall's return for one month. One row is one note written on one day. Once that sentence exists, the store has a rule it can apply mechanically, and the rule is that no two rows may claim to be the same one.
The market office's 32 rows are therefore offered to a store five times, changing nothing except that sentence, and what gets in is counted each time. One conventionA decision about how something is to be counted, fixed in advance and recorded, so that the resulting figure does not depend on who happened to produce it. has to be stated before the counts mean anything: the loader here reads the file from the top and keeps the first row carrying a given combination, turning away any later row that repeats it. First one wins. Say it out loud. The convention matters enormously in a moment.
| The declared key, in words | What it claims one row is | Accepted | Turned away |
|---|---|---|---|
| stall_id | One row is one stall | 8 | 24 |
| stall_name | One row is one stall name | 9 | 23 |
| stall_id and month | One row is one stall's return for one month | 31 | 1 |
| stall_name and month | One row is one stall name's return for one month | 31 | 1 |
| stall_id, month and filed_on_day | One row is one row | 32 | 0 |
Read the ladder from the top and it looks like a story of progress: 8, then 9, then 31, then 31, then everything. It is not. The first declaration is a real claim about the world and it is simply wrong for this table, so the store throws out 24 rows of the 32 offered and is right to. The second declaration is the same wrong claim asked in a worse language, and the extra row it lets in is not a ninth stall, it is a second spelling. The third and fourth are the true grain of this table. The fifth is not a key at all: a declaration wide enough to accept every row offered is a row number wearing a key's clothes, and adopting one is how a store is quietly made to agree with a file instead of checking it.
Somebody widens the key to stall_id, month and filed_on_day, watches all 32 rows load, and calls it a clean run. What is the reply?
The panel below widens the key one column at a time. Before it runs: what does the count of turned away rows do across the four steps?
Move the declared key and watch the store change its mind about what a row is.
One control, and it moves one thing: which of the five declarations the store was given before the load began. Everything else is recomputed from the same 32 rows. The grid is the record drawn as it actually is, eight stalls across and four months down, so a solid tile is a row the store let in and a pale tile is a row it turned away. The one stall month the office filed twice sits as a split tile, and the one stall month with no row at all sits as a dashed outline. The bar underneath is the same count as a length, and the lines below it name what was refused. The panel opens on the stall and the month, a declaration that accepts 31 rows of the 32 offered.
Educational illustration. The Neelbagh market, its stall record and its day book were made up for teaching. The 32 rows offered are the file exactly as the market office handed it over, the loader keeps the first row carrying a combination and turns away any later repeat, and a declared key checks the grain and nothing else. Every quantity in the panel is a count of rows, so no quantity is rounded and none can fall below zero.
Declared on the stall and the month, the store refuses exactly one row. Before reading on: which fault does it catch, and which will it certainly miss?
What does a key catch, and what does it walk straight past?
Declared on the stall and the month, the store refuses exactly one row of the 32 and it names which one. NB-03, month 2. The market office filed a return for Harit Greens in month 2 on day 5 reading Rs 36,400/-, and filed a second one for the same stall and the same month on day 19 reading Rs 39,700/-. In the plain file those are two lines that look perfectly ordinary and nothing anywhere says one contradicts the other. In the store they are one contradiction with a name attached to it. A silent fault has been turned loud, and turning a silent fault loud is the single most valuable thing a declared shape does.
Now look closely at which of the two the store kept. The answer is uncomfortable, and it is the reason a refusal is a beginning rather than an ending. The loader keeps the first row carrying the combination, and the first row here is the one filed on day 5. The day book is the market office's running log of what actually happened, and against day 19 it records that the return was revised and the first figure withdrawn. So the store has kept the withdrawn figure and turned away the corrected one. The store did nothing wrong: it was asked whether two rows claim to be the same stall month, and it answered. Nobody asked it which of the two figures is true, and nothing about it could ever answer that.
Now the other side of the boundary, and it is the more important side. The very same store, running the very same declaration, accepts both spellings of NB-03 without a murmur. The market office wrote Harit Greens on the returns for months 1 and 2 and Harit Green on the returns for months 3 and 4, so the stored returns table holds 9 distinct stall names for 8 stalls. Not one line of any load report mentions it. Why would it? The key names stall_id, the stall identifier is NB-03 on all four rows, the grain is intact, and stall_name is simply a column the row happens to also carry.
Think of the ruled ledger again. The printed boxes stop the clerk writing two figures where one belongs. The ruling was never a claim about spelling, so the printed boxes do nothing whatsoever about the clerk spelling a stall's name differently in April from how it was spelled in January. A key checks the grain and nothing else, and a fault that leaves the grain intact walks straight past it. Sort the record's nine known faults by whether they break the declared grain and whether they break a declared type, and seven of the nine sit in the quadrant a store cannot see at all.
The stored returns table holds 9 distinct stall names for 8 stalls and no load report mentioned it. Why not, and what would have caught it?
Why does the office keep two tables rather than one?
The market office keeps a second thing besides the returns, and it is the running log the clerk writes as events happen: the day book. Twelve notes, one line per note, each carrying a stall, the stall's name as the office wrote it, a month, the day the note was made and the note itself. The twelve notes touch 8 stalls and 11 stall months. Exactly one stall month carries two of them: NB-03 in month 2, precisely the stall month the returns table argued about.
The obvious question is why the office does not just bolt the notes onto the returns and keep one wide table. The answer is one word long and it is the same word the whole of this reading has been circling. The two tables have different grains, and a table can declare only one. One row of the returns is one stall's return for one month. One row of the day book is one note written on one day. Merging them means the very first stall month with two notes forces a choice: either that stall month becomes two rows, and the returns table quietly stops being one row per stall month, or one of the two notes gets thrown away.
Think of a household with a bank passbook and a diary. The passbook has one line per transaction. The diary has one line per day, and some days carry three entries and some carry none. Nobody in the household tries to keep one book, and not because they lack the stationery. The household keeps two because the two books answer different questions, and each book only stays trustworthy while it is answering its own. Putting the two side by side, so a note lands against the return it explains, is a genuinely separate operation with a genuinely separate way of going wrong, and it is covered further on rather than here.
In one word, why does the market office keep the returns and the day book as two tables, and what is lost by merging them?
What a store still does not tell
Three things, and each of them is a reason not to mistake a declared shape for a guarantee.
A store does not say whether a figure is right. A store says whether a figure fits. NB-08's month 3 return reads Rs 4,80,000/- where the day book records a slip reading Rs 48,000/-, and no declaration in the world catches that. A whole number of rupees is exactly what the column was promised and exactly what it got. A shape is a filter, not a judgement.
A store does not report what it refused unless somebody goes and looks. The refusal count is written into a load report by a machine that has no idea whether a human being will ever open it, and the entire value of the store collapses to nothing at the moment that report stops being read.
And it carries no provenanceA figure's history: the place it started and each thing done to it since. Nothing inside the figure carries that history, so it survives only where somebody kept notes while the work was happening. of its own. The store knows what it currently holds. The store does not know where those values came from, who typed them, or which of them was corrected on the way. A store will also hand rows back in whatever order suits it, so row orderWhere a line happens to sit in the stack. Shuffle the same lines and the order changes while the record does not, which is why nothing should ever be read off position alone. out of a store is a fact about the store rather than about the market. A declared shape is a filter and not a judgement, and treating a clean load as a verdict on the data is where the expensive mistakes begin.
How this goes wrong: the refusal nobody reads, and the key that was widened until it stopped complaining
A team loads the Neelbagh returns into a store declared on the stall and the month. The loader refuses one row and writes the refusal into the load report, correctly, by name. Nobody opens the report. The load is recorded as done and the work moves on.
Six weeks later somebody notices the loader keeps flagging something and finds the refusal line. The fix chosen is to widen the key to include the filing day. Both NB-03 rows are then legitimately different and the load will run clean. And the load does run clean. All 32 rows go in, the report says nothing, and everybody is satisfied.
The fault has not been fixed. The fault has been declared away. The stall month for NB-03 in month 2 is now in the table twice, contributing Rs 36,400/- and Rs 39,700/- to every total anyone computes. The two together make Rs 76,100/- for a month the market office says took Rs 39,700/-. The withdrawn return now sits permanently alongside the revised one, no part of the system will ever mention it again, and the store has stopped checking the one thing it was checking before. The old load report at least argued with the file. The new one agrees with it.
The fix is not technical, and that is why it keeps not getting done. Read the refusal count on every single load, and treat a widened key as what it is: a change in what a row of the table means, not a change of setting.
Four questions to ask of any stored table somebody hands over
Stored tables arrive far more often than they are built, usually with the words it is all in the database and no further detail. The four questions below take about a minute and they are the whole of the job.
- What is the grain, in one sentence? If the person handing it over cannot finish the sentence one row of this table is one, then nobody has decided, and every count taken off it will be counting something nobody has named.
- What is the key, and how many rows did the last load refuse? Both halves matter. A key with no refusal count attached is a claim with no evidence, and a refusal count of zero on a record anybody has ever typed by hand deserves a second look rather than relief.
- Which columns are identifiers stored as numbers? Ask for them by name. Every one of them is an invitation to produce a figure like 2426.375, and the figure will look every bit as respectable as a real one.
- What is the convention behind each figure computed from it, with its denominatorWhatever an average was divided by. Change it and the same total becomes a different average without any arithmetic being wrong anywhere. named? An average of takings over the stall months that carry a usable figure and an average over every row in the table are different questions, and the store will answer both without ever mentioning that they differ.
A lender assessing a small market operator, an analyst rebuilding a set of monthly figures and a household reconciling a passbook against a diary are all doing the same four checks in different clothes. A stored table handed over without its refusal count is a table whose faults have been declared away rather than found, and the person handing it over usually does not know that.
Somebody hands over a stored table and says it loaded cleanly. Which two questions come first, and what does each protect against?
Where this reading stops. The written down path a record travels from raw source to an analysis ready table, and the shape of a request and the answer that comes back, were both settled earlier and are not repeated here. Working with the table once it has been pulled into memory, and grouping it to get one figure per stall, comes further on. So does setting one table beside another so that a note lands against the return it explains, and that is where the most expensive damage in this subject sits. So does putting a precise question to a stored table, and so do the ordered cells that let a whole analysis be run again from the top. Administering a store, making it answer faster and backing it up are not a finance analyst's work at all.
What was consulted to write this, and why is nothing the honest answer?
No outside document was read, and that is a finding about the subject rather than a gap in the work. The count is a consequence of two things a reader can hold in one hand: a set of rows, and a sentence saying what one row is. Counting how many rows a declared shape lets in therefore requires nobody's authority. A second person given those two will land on 8, 9, 31, 31 and 32 without needing to trust anyone. The table below says where each ingredient of that arithmetic was settled and how it can be tested here.
| What a figure above rests on | Where it was settled | How to test it against this reading |
|---|---|---|
| The 32 rows, the eight columns and the nine faults | The Neelbagh stall record, fixed in an earlier reading on record quality and reused here without a single change | Count the tiles in the panel. Eight stalls across, four months down, one stall month split in two and one missing |
| The five accepted counts of 8, 9, 31, 31 and 32 | Counting distinct combinations of the named columns across those same 32 rows | Take any declaration, list the combinations it names, strike out the repeats and count what is left |
| The empty average of 2426.375 | The eight licence numbers from 2104 to 2743, added to 19,411 and divided by the eight stalls | Add the eight values printed in the figure. The division is exact and stops after three places |
| The twelve day book notes over 8 stalls and 11 stall months | The market office's running log, the one object added to the record beyond the returns | Count the notes named in the two table figure and check that only NB-03 in month 2 carries two |
| Any rule, rate, threshold, filing period or published standard | None is stated anywhere above. A count of rows in a made up file needs none | There is no outside value to confirm at source. Such a value met elsewhere is taken from whoever issues it |
The Neelbagh covered market, the office that runs it, the stall record it keeps, the day book beside it and every stall named in them are invented.
Educational material. Not advice on any investment, tax, budget or market position.