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.

Data Collection

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.

amt

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.

cd

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; createUdt and 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 create and 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:

Column Naming Rules

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.

Table Naming Rules

Singular Table names are always singular; Goose rather tan Geese, Crow as opposed to Crows, and Whale, not Whales. Simple enough. However, by saying singular, that does not preclude collective terms. So Gaggle, Murder and Pod would all be perfectly good table names, whereas Gaggles, Murders and 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 CustInfoTbl, GENERALINFO and 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? …