SQL Server: Example Naming Convention

An example of a SQL Server naming convention I have encountered:

Tables

  • tcTableName (e.g. tcAsset) for core tables, i.e. tables that contain regularly changing data and have insert/update/delete queries run against them.
  • trTableName (e.g. trAssetType) for reference tables, i.e. tables that contain static/unchanging data, usually used in reference to data in core tables.
  • tjTableName (e.g. tjUserRole) for join tables, i.e. tables that provide a many-to-many join between two other tables.

Columns

  • tableNameId (e.g. assetId) for primary keys.
  • foreignTableNameId (e.g. assetTypeId) for foreign keys.
  • name” for text fields in simple ID/Name tables, e.g. in trAssetType (assetTypeID int, name nvrachar(100)).
  • lowerUpperUpper (e.g. propertyEntranceStorey) for all other columns, with no particular convention on the column names other than the lowerUpperUpper case.

Views

  • vwTableNameSubset (e.g. vwAsset, vwWindowHistory, vwWindowHistoryLatest)

Other

  • Stored Procedures: uspTableAction (e.g. uspAssetDelete) (usp = User Stored Procedure – don’t use “sp” as its reserved for system stored procedures and SQL Server always checks the Master database for the SP first before checking the current database, meaning its bad for performance).
  • Functions: fnXXX
  • User-defined Data Types: uddtXXX
  • ID Columns: tableID / foreignTableID (e.g. assetID / assetTypeID)

What Makes Technology Popular?

It is easy to assume that technology becomes popular simply because it is better than the competition. But as the saying goes, “that ain’t necessarily so”. In fact, the history of innovation is replete with examples of inferior

technologies capturing a larger market share than their technically superior rivals.

Some of the best known (and hotly debated) examples of include:

  1. Betamax / VHS
  2. Ethernet / Token Ring
  3. Mac / Windows
  4. Firefox / Internet Explorer

Moreover, there are also plenty of perfectly good technologies that die a death because they lag far behind their rivals in terms of sales, despite their being little difference in their technical merit. Most format wars are a case in point.

So, why does this happen? How does a technology come to dominate the market, often at the expense of other, sometimes superior, alternatives?

The answer lies in the way that individuals make decisions about the technology they use. The technology that gets picked for three reasons:

  1. It meets a need (or at least appears to)
  2. It gets publicity – sometimes through deliberate advocacy, sometimes through word of mouth
  3. There costs of adoption are outweighed by the benefits

To illustrate, we’ll consider a well-known example: the Windows operating system.

Firstly, users who get what they need from Windows are unlikely to go looking for alternatives. They might do so, however, if they start to believe that their are problems with Windows that reduce their productivity. For the majority of Windows users, however, Windows meets their needs. Whether that is because it is a great product or because the users don’t know any different is irrelevant. The point is that for many users, it is simply good enough.

Secondly, even if they go looking, Windows users are unlikely to find out about many of the alternatives. This is because they just don’t receive very much attention from developers, other users nor the press. How many Windows users have heard of the Haiku Operating System, for example? On the other hand, it is more likely that they will have heard of the Mac or Linux.

Finally, even if Windows users find an alternative, they won’t switch unless the benefits of switching outweigh the costs of adopting the new platform. If critical applications are available on the new platform, users might switch. If not, they are unlikely to do so. Similarly, if the learning curve is steep, they may be reluctant, but if the user experience is similar, they may be more willing to do so. Ubuntu is a popular Linux distribution because of the focus on usability, whereas others are less so because they’re difficult to get used to.

So, there you have it: technology is not popular just because it is good (although that helps), but because of market forces; marketing in particular.

Thanks to the jgauffin for the inspiration behind this post.