It tends to a get a bad rap these days. But we recently challenged ourselves to try and use Twitter for a better and higher purpose than has often been the case recently. In particular, we’re attempting to tackle the thorny issue of the sacrifices we have all been asked to make in the face of the implacable enemy of COVID19.
We started with the idea of how what we are being asked to do compares to the sacrifices made by other generations when faced with similar, global calamaties. World War II was the first thing which came to mind both in terms of its impact and it’s scale. However, to avoid a comparison of the two conflicts — which is comparing two really incomparable events — we chose to focus on the progress of the disasters on a day-by-day basis and the impact to ordinary citizens. …
We always assumed a tweet’s best day for impressions is the first. We also assumed the number of impressions steadily declines thereafter. It turns out this may not be the case. Based on some initial, rudimentary analysis we have found a counterintuitive ‘echo’ on day five where tweets perform better than days two through four. There is also evidence of a less pronounced echo on day 12 and some arguable evidence there is a still smaller third echo around the 21st day.
Rather than seeing the data through the filter of somebody else’s analysis, we wanted to get as close to the source as Twitter will allow: this is Twitter Analytics which many may not have even noticed, let alone turned on. It is worth it, however, as it is a potential treasure trove of raw data available to anyone — even those who have not signed up as a Twitter developer. …
In Part II: Column Names, the notion of Base Names was introduced. Rather than clutter that article up with the current list, I chose to simply segregate these off into their own article.
An amount, regardless of whether it is an integer or floating point quantity. An example would be Transaction.amt. amt, in this case, refers to the quantity of money associated with the transaction.
A very short code that provides direct identifies subtypes of records in the table. Example:
Transaction.cd directly identifies the type of transaction (eg.
IN for invoice,
CR for credit note) It’s important that the codes chosen reflect the nature of what they encode.
A, for invoice, would be a bad choice. …
In Part III of this series, I talked about the naming of key columns. This time, I turn my attention to two fields which really should be in every table: when attempting to track down problems in the database, a very useful thing to know is the date and time the data was first created, and when it was most recently modified. As part of a standardized approach to database design, I have gotten into the habit of adding two columns to every table definition;
modifyUdt that provide this capability.
What’s In a Name? If you’ve read the post on column naming standards, you will recognize the Base Name
udt. This means the column reflects UTC time, not local time. This is a nod toward internationalization, which I think every application should take into consideration, particularly in the early stages of design and implementation. The prefixes
modify are self-explanatory, and are required, of course, to differentiate between the two types of dates in a given table definition. Finally, the two dates have equal status and value, and therefore they both have prefixes. …
Having covered the basic naming conventions for tables and columns, I’m now going to cover standards for naming primary and foreign key columns in a table. For those of you who thought Part I and Part II were, well — a little pedestrian — stay tuned for a real magic show in this post. Or at least that will be true if you lead a pretty sheltered life.
By definition, a foreign key involves two tables: the table that is referring to information in another table, and the table that is being referenced. …
In Part I: Tables, I discussed standards as they relate to naming tables. This time, it’s:
Base Names The concepts modeled with database tables tend to be described in similar terms. Many tables contain a column with descriptive information, a column with a name or a column with amounts, for example. For these, it’s useful to have a list of standardized column names used repeatedly. These are referred to as Column Base Names, or just Base Names. The most important concept is that the table in which the Base Name is found provides a context. If you are using one of the Base Names by itself, as will often be the case, it is inferred that the thing being described with the
dsc Base Name or named with the
nm Base Name is the idea being modeled in the table in which these columns are found. More to the point, it’s not necessary to repeat some portion of the table name itself within the column name. For example,
Whale.nm (think ‘whale name’) reads a lot better than
Whale.whaleNm (think ‘whale whale name’). …
This is the first part in a series of posts on database object naming standards. In the previously posted Introduction, I laid out the case for the value of using these naming standards in general, and in this post, I talk specifically about table names.
Singular Table names are always singular;
Goose rather tan
Crow as opposed to
Whales. Simple enough. However, by saying singular, that does not preclude collective terms. So
Pod would all be perfectly good table names, whereas
Pods would not. To understand why, it helps to think about the columns that will eventually populate this table, and in particular, how the combination of table name and column name will sound when read or discussed. I would argue that
Crow.nm (think ‘crow name’) and
Crow.dsc (‘crow description’), make more sense than the plural equivalents (‘crows name’ and ‘crows description’). …
Physical database objects are often treated likely the shirttail cousins of modern, object-oriented software development. How often have you peaked below the covers of a seemingly well-designed and well-constructed application, and been appalled and totally baffled by the underlying physical database structure, with its mish-mash of
STUD_MASTER (as in student master) tables. Some would argue this nastiness can be wrapped up in a data access layer, and the underlying physical structure never seen again. But the data in your application has to be physically stored somewhere sooner or later, so you’re likely to run into the structure of that data at some point. When you do, you’ll have to hope the person who created it — maybe even you? …