3 ms·

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 k

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
- westurner 6y ago
  In terms of database normalization, delimiting multiple fields within a column name field violates the "atomic columns" requirement of the first though sixth normal forms (1NF - 6NF) https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization Are there standards for storing columnar metadata (that is, metadata about the columns; or column-level metadata)? In terms of columns, SQL has (implicit ordinal, name, type) and then primary key, index, and [foreign key] constraints. RDFS (RDF Schema) is an open W3C linked data standard. An rdf:Property may have a rdfs:domain and a rdfs:range; where the possible datatypes are listed as instances of rdfs:range. Primitive datatypes are often drawn from XSD (XML Schema Definition), or https://schema.org/ https://schema.org/ . An rdfs:Class instance may be within the rdfs:domain and/or the rdfs:range of an rdf:Property. RDFS is generally not sufficient for data validation; there are a number of standards which build upon RDFS: W3C SHACL (Shapes and Constraint Language), W3C CSVW (CSV on the Web). There is some existing work on merging JSON Schema and SHACL. CSVW builds upon the W3C "Model for Tabular Data and Metadata on the Web"; which supports arbitrary "annotations" on columns. CSVW can be represented as any RDF representation: Turtle/Trig/M3, RDF/XML, JSON-LD. https://www.w3.org/TR/tabular-data-primer/ https://www.w3.org/TR/tabular-data-primer/ https://www.w3.org/TR/tabular-data-model/ https://www.w3.org/TR/tabular-data-model/ : > an annotated tabular data model: a model for tables that are annotated with metadata. Annotations provide information about the cells, rows, columns, tables, and groups of tables […] ... From https://twitter.com/westurner/status/901992073846456321 https://twitter.com/westurner/status/901992073846456321 : > "7 metadata header rows (column label, property URI path, DataType, unit, accuracy, precision, significant figures)" https://wrdrd.github.io/docs/consulting/linkedreproducibility#csv-csvw-and-metadata-rows https://wrdrd.github.io/docs/consulting/linkedreproducibilit... ... From https://twitter.com/westurner/status/1295774405923147778 https://twitter.com/westurner/status/1295774405923147778 : > Relevant: https://discuss.ossdata.org/ https://discuss.ossdata.org/ topics: "Linked Data formats, tools, challenges, opportunities; CSVW, https://schema.org/Dataset https://schema.org/Dataset , https://schema.org/ScholarlyArticle https://schema.org/ScholarlyArticle " https://discuss.ossdata.org/t/linked-data-formats-tools-challenges-opportunities-csvw-schema-org-dataset-schema-org-scholarlyarticle/160 https://discuss.ossdata.org/t/linked-data-formats-tools-chal... > "A dataframe protocol for the PyData ecosystem" https://discuss.ossdata.org/t/a-dataframe-protocol-for-the-pydata-ecosystem/267 https://discuss.ossdata.org/t/a-dataframe-protocol-for-the-p... > A .meta protocol should implement the W3C Tabular Data Model: [...] ... The various methods of doing CSV2RDF and R2RML (SQL / RDB to RDF Mapping) each have a way to specify additional metadata annotations. None stuff data into a column name (which I'm also guilty of doing with e.g. "columnspecs" in a small line-parsing utility called pyline that can cast columns to Python types and output JSON lines). ... Even JSON5 is insufficient when it comes to representing e.g. complex fractions: there must be a tbox (schema) in order to read the data out of the abox (assertions; e.g. JSON). JSON-LD is sufficient for representation; and there are also specs like RDFS, SHACL, and CSVW. Abox: https://en.wikipedia.org/wiki/Abox https://en.wikipedia.org/wiki/Abox
deleted 6y ago
[deleted]

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

westurner 6y ago
In terms of database normalization, delimiting multiple fields within a column name field violates the "atomic columns" requirement of the first though sixth normal forms (1NF - 6NF) https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization Are there standards for storing columnar metadata (that is, metadata about the columns; or column-level metadata)? In terms of columns, SQL has (implicit ordinal, name, type) and then primary key, index, and [foreign key] constraints. RDFS (RDF Schema) is an open W3C linked data standard. An rdf:Property may have a rdfs:domain and a rdfs:range; where the possible datatypes are listed as instances of rdfs:range. Primitive datatypes are often drawn from XSD (XML Schema Definition), or https://schema.org/ https://schema.org/ . An rdfs:Class instance may be within the rdfs:domain and/or the rdfs:range of an rdf:Property. RDFS is generally not sufficient for data validation; there are a number of standards which build upon RDFS: W3C SHACL (Shapes and Constraint Language), W3C CSVW (CSV on the Web). There is some existing work on merging JSON Schema and SHACL. CSVW builds upon the W3C "Model for Tabular Data and Metadata on the Web"; which supports arbitrary "annotations" on columns. CSVW can be represented as any RDF representation: Turtle/Trig/M3, RDF/XML, JSON-LD. https://www.w3.org/TR/tabular-data-primer/ https://www.w3.org/TR/tabular-data-primer/ https://www.w3.org/TR/tabular-data-model/ https://www.w3.org/TR/tabular-data-model/ : > an annotated tabular data model: a model for tables that are annotated with metadata. Annotations provide information about the cells, rows, columns, tables, and groups of tables […] ... From https://twitter.com/westurner/status/901992073846456321 https://twitter.com/westurner/status/901992073846456321 : > "7 metadata header rows (column label, property URI path, DataType, unit, accuracy, precision, significant figures)" https://wrdrd.github.io/docs/consulting/linkedreproducibility#csv-csvw-and-metadata-rows https://wrdrd.github.io/docs/consulting/linkedreproducibilit... ... From https://twitter.com/westurner/status/1295774405923147778 https://twitter.com/westurner/status/1295774405923147778 : > Relevant: https://discuss.ossdata.org/ https://discuss.ossdata.org/ topics: "Linked Data formats, tools, challenges, opportunities; CSVW, https://schema.org/Dataset https://schema.org/Dataset , https://schema.org/ScholarlyArticle https://schema.org/ScholarlyArticle " https://discuss.ossdata.org/t/linked-data-formats-tools-challenges-opportunities-csvw-schema-org-dataset-schema-org-scholarlyarticle/160 https://discuss.ossdata.org/t/linked-data-formats-tools-chal... > "A dataframe protocol for the PyData ecosystem" https://discuss.ossdata.org/t/a-dataframe-protocol-for-the-pydata-ecosystem/267 https://discuss.ossdata.org/t/a-dataframe-protocol-for-the-p... > A .meta protocol should implement the W3C Tabular Data Model: [...] ... The various methods of doing CSV2RDF and R2RML (SQL / RDB to RDF Mapping) each have a way to specify additional metadata annotations. None stuff data into a column name (which I'm also guilty of doing with e.g. "columnspecs" in a small line-parsing utility called pyline that can cast columns to Python types and output JSON lines). ... Even JSON5 is insufficient when it comes to representing e.g. complex fractions: there must be a tbox (schema) in order to read the data out of the abox (assertions; e.g. JSON). JSON-LD is sufficient for representation; and there are also specs like RDFS, SHACL, and CSVW. Abox: https://en.wikipedia.org/wiki/Abox https://en.wikipedia.org/wiki/Abox

deleted 6y ago

[deleted]