7 ms·

Column Names as Contracts

55 points by MaysonL 6y ago

closed 6y ago
> Using controlled vocabularies for column names is a low-tech, low-friction approach to building a shared understanding of how each field in a data set is intended to work. Can't stress how useful this has been working on data science teams. There is really an art for getting a group of people to standardize around a vocabulary of column name pre/suffixes without being overly prescriptive. Being able to unlock nifty tools / behaviors by naming your columns certain ways seems crazy powerful. (Like a form of function dispatching)
- lowwave 6y ago
  Yeah convention over configuration is great! It attributed to the great success of RoR kind of framework as well.
Cieplak 6y ago
This sounds a lot like Hungarian notation [1]. [1] https://en.wikipedia.org/wiki/Hungarian_notation https://en.wikipedia.org/wiki/Hungarian_notation
- goto11 6y ago
  Yeah, and like Hungarian notation it is mostly useful in an untyped system (like BCPL where Hungarian was invented). If you have a type system, let it do the work for you. One of the most bizarre conventions I have seen is prefixing table names with "TBL_" and column names "COL_". I suspect some people just really like abbreviations.
  - mgkimsal 6y ago
    Haven't seen a COL_ prefix in a long time, but I still run across 'TBL_' prefixes sometimes. I've seen it in practice for more than 20 years, and never saw any value. Then... I realized you might be pulling from a 'view' vs an actual table, and perhaps there's a bit of value in that knowledge? I did run across someone joining a view against a table that was joining another view that was comprised of joining against that original table, and the query then did a few of these, and people wondered why it got slow. If the names had been prefixed with view_ perhaps it might have been more obvious this might cause problems? I think not though - the original person who put it together was apparently a DBA by trade and insisted this was normal and good, and 'my' code (which... he'd also written?) was the culprit. "Worked fine when I was there". Well... there were 1200 rows at that time; hitting 60000 rows, we ran in to large perf problems.
  - jpalomaki 6y ago
    I think the goal is slightly different: “This is not quite the same as data types in a programming language (e.g. bool, double, float) although everything with the same prefix should ultimately be cast in the same type. Instead, these data types imply both a type of information and appropriate usage patterns [...]”
paperwork 6y ago
I really like the idea of being more thoughtful about naming columns and being more explicit about the “type” of data contained in them. Is this idea already known among data modelers or data engineers? I’d love to read any other references, if available.
- sixdimensional 6y ago
  I am a data architect in my day job. Within the realm of data management, I'd say "metadata management" [1] is the general category this fits within. I would say, yes this idea is known/very common, as data architecture is as much about the descriptive language we use as anything. I mean, "business glossaries", taxonomy, even just naming conventions [2] in coding, these are all related. If you build enough databases/tables or even code yourself, you inevitably come across the "how to name things" problem [3]. If all you have to sort on for the known meaning of a thing (column, table, file, etc.) is a single string value, then encoding meaning into it is quite common. This way, a sort creates a kind of "grouping". Many database vendors follow standard naming conventions - such as Oracle, for example [4]. It is considered a best practice when designing/building the metadata for a large system, to establish a naming convention. Among other things, it makes finding things easier, as well as all the potential for automation. You get all kinds of variations on this, such as, should the "ID_" come as a prefix or a suffix (i.e. "_ID"). One's initial thought is to use it as a prefix so all the related types group together, but then that becomes much more difficult if you want to sort items by their functional area (e.g. DRIVER_ID, DRIVER_IND, etc.). One other place you see something similar is in "smart numbers" which is an eternal argument - should I use a "dumb identifier" (GUID, integer) or a "smart one" (one encoding additional meaning) [5]. I mean, basically, any time you can encode information in the meta-data of data, I think you can then operate on it by following "convention over configuration" (as mentioned elsewhere in the discussion comments). The only problem I see is that such conventions can, at times be limiting - depending on the length of your metadata columns, and the variability you are trying to capture - which is why I believe, generally, metadata is often better separated and linked to the data it describes - this decoupling allows for much more descriptive metadata than one could encode in simple a single string value. Certainly, you can get a long way with an approach like this, but I suspect you would run into 80/20 rule limitations. Using naming in this way is a form of tight coupling, which could be seen as an anti-pattern in terms of meta-data flexibility, in some cases. [1] https://en.wikipedia.org/wiki/Metadata_management https://en.wikipedia.org/wiki/Metadata_management [2] https://en.wikipedia.org/wiki/Naming_convention_(programming) https://en.wikipedia.org/wiki/Naming_convention_(programming... [3] https://martinfowler.com/bliki/TwoHardThings.html https://martinfowler.com/bliki/TwoHardThings.html [4] https://oracle-base.com/articles/misc/naming-conventions https://oracle-base.com/articles/misc/naming-conventions [5] https://en.wikipedia.org/wiki/Smart_number https://en.wikipedia.org/wiki/Smart_number
ZeroClickOk 6y ago
Now I remember my pain to work in a system with hundreds of tables and near 4-5 different column name schemas :/
fmjrey 6y ago
Reminds me of what clojure spec is trying to do: create data specifications that can be named and reused via a registry. The metadata is just data on the shape of the data so it can serve as doc, validation, data generation, property testing, etc. Spec names are namespaced but no specific guidelines are given it's just organizational. Separately clojure also allows for namespaced names for map entries, but again provides no specific guidelines on what namespaces should be: in code this could be a library name, but it could also be an entity name as suggested by datomic best practices. The combination of both features, namespaced var/entry names associated with namespaced specs, seems to make more sense than using name parts conventions, but if you're not clojure all the way then the latter might be the only way.
anamax 6y ago
There are two hard problems in computer science. Naming things, cache invalidation, and off-by-one errors. The first is about people, not algorithms.

55 points by MaysonL 6y ago

> Using controlled vocabularies for column names is a low-tech, low-friction approach to building a shared understanding of how each field in a data set is intended to work. Can't stress how useful this has been working on data science teams. There is really an art for getting a group of people to standardize around a vocabulary of column name pre/suffixes without being overly prescriptive. Being able to unlock nifty tools / behaviors by naming your columns certain ways seems crazy powerful. (Like a form of function dispatching)

lowwave 6y ago
Yeah convention over configuration is great! It attributed to the great success of RoR kind of framework as well.

Cieplak 6y ago

This sounds a lot like Hungarian notation [1]. [1] https://en.wikipedia.org/wiki/Hungarian_notation https://en.wikipedia.org/wiki/Hungarian_notation

goto11 6y ago
Yeah, and like Hungarian notation it is mostly useful in an untyped system (like BCPL where Hungarian was invented). If you have a type system, let it do the work for you. One of the most bizarre conventions I have seen is prefixing table names with "TBL_" and column names "COL_". I suspect some people just really like abbreviations.
- mgkimsal 6y ago
  Haven't seen a COL_ prefix in a long time, but I still run across 'TBL_' prefixes sometimes. I've seen it in practice for more than 20 years, and never saw any value. Then... I realized you might be pulling from a 'view' vs an actual table, and perhaps there's a bit of value in that knowledge? I did run across someone joining a view against a table that was joining another view that was comprised of joining against that original table, and the query then did a few of these, and people wondered why it got slow. If the names had been prefixed with view_ perhaps it might have been more obvious this might cause problems? I think not though - the original person who put it together was apparently a DBA by trade and insisted this was normal and good, and 'my' code (which... he'd also written?) was the culprit. "Worked fine when I was there". Well... there were 1200 rows at that time; hitting 60000 rows, we ran in to large perf problems.
- jpalomaki 6y ago
  I think the goal is slightly different: “This is not quite the same as data types in a programming language (e.g. bool, double, float) although everything with the same prefix should ultimately be cast in the same type. Instead, these data types imply both a type of information and appropriate usage patterns [...]”

paperwork 6y ago

I really like the idea of being more thoughtful about naming columns and being more explicit about the “type” of data contained in them. Is this idea already known among data modelers or data engineers? I’d love to read any other references, if available.

sixdimensional 6y ago
I am a data architect in my day job. Within the realm of data management, I'd say "metadata management" [1] is the general category this fits within. I would say, yes this idea is known/very common, as data architecture is as much about the descriptive language we use as anything. I mean, "business glossaries", taxonomy, even just naming conventions [2] in coding, these are all related. If you build enough databases/tables or even code yourself, you inevitably come across the "how to name things" problem [3]. If all you have to sort on for the known meaning of a thing (column, table, file, etc.) is a single string value, then encoding meaning into it is quite common. This way, a sort creates a kind of "grouping". Many database vendors follow standard naming conventions - such as Oracle, for example [4]. It is considered a best practice when designing/building the metadata for a large system, to establish a naming convention. Among other things, it makes finding things easier, as well as all the potential for automation. You get all kinds of variations on this, such as, should the "ID_" come as a prefix or a suffix (i.e. "_ID"). One's initial thought is to use it as a prefix so all the related types group together, but then that becomes much more difficult if you want to sort items by their functional area (e.g. DRIVER_ID, DRIVER_IND, etc.). One other place you see something similar is in "smart numbers" which is an eternal argument - should I use a "dumb identifier" (GUID, integer) or a "smart one" (one encoding additional meaning) [5]. I mean, basically, any time you can encode information in the meta-data of data, I think you can then operate on it by following "convention over configuration" (as mentioned elsewhere in the discussion comments). The only problem I see is that such conventions can, at times be limiting - depending on the length of your metadata columns, and the variability you are trying to capture - which is why I believe, generally, metadata is often better separated and linked to the data it describes - this decoupling allows for much more descriptive metadata than one could encode in simple a single string value. Certainly, you can get a long way with an approach like this, but I suspect you would run into 80/20 rule limitations. Using naming in this way is a form of tight coupling, which could be seen as an anti-pattern in terms of meta-data flexibility, in some cases. [1] https://en.wikipedia.org/wiki/Metadata_management https://en.wikipedia.org/wiki/Metadata_management [2] https://en.wikipedia.org/wiki/Naming_convention_(programming) https://en.wikipedia.org/wiki/Naming_convention_(programming... [3] https://martinfowler.com/bliki/TwoHardThings.html https://martinfowler.com/bliki/TwoHardThings.html [4] https://oracle-base.com/articles/misc/naming-conventions https://oracle-base.com/articles/misc/naming-conventions [5] https://en.wikipedia.org/wiki/Smart_number https://en.wikipedia.org/wiki/Smart_number

ZeroClickOk 6y ago

Now I remember my pain to work in a system with hundreds of tables and near 4-5 different column name schemas :/

fmjrey 6y ago

Reminds me of what clojure spec is trying to do: create data specifications that can be named and reused via a registry. The metadata is just data on the shape of the data so it can serve as doc, validation, data generation, property testing, etc. Spec names are namespaced but no specific guidelines are given it's just organizational. Separately clojure also allows for namespaced names for map entries, but again provides no specific guidelines on what namespaces should be: in code this could be a library name, but it could also be an entity name as suggested by datomic best practices. The combination of both features, namespaced var/entry names associated with namespaced specs, seems to make more sense than using name parts conventions, but if you're not clojure all the way then the latter might be the only way.

anamax 6y ago

There are two hard problems in computer science. Naming things, cache invalidation, and off-by-one errors. The first is about people, not algorithms.