write down conventions about lter-core-metabase HOT 5 OPEN

conventions about naming (col, table and views):

1. externally facing views intended for EML use the EML element or attribute name lower-cased.
2. tables inherited from GCE retain the multicase naming they had in Wade's version (we may or may not continue with this going forward, but it can help you understand why things are/were named that way)
3. views not having to do with writing EML may use other naming conventions (such as those for package management.)
4. tables that are controlled vocabs are prefixed "cv_", when they are post-Wade-era (Wade appended the string *List" for this
5. views are prefixed "vw_".

from lter-core-metabase.

Gastils other notes: https://github.com/lter/LTER-core-metabase/blob/master/docs/discuss_design_and_naming_patterns

from lter-core-metabase.

Naming conventions I'm pretty good on at this point. What is still unclear is what criteria to decide what goes in what schema; and this I have little experience on and therefore can go with whichever. I do think we should decide on conventions and write them down sooner than later though.

an observation to add to 4:
CV tables in pkg_mgmt are prefixed "cv_" while they are suffixed "*List" in lter_metabase. Except for some cases. I also think "List" are meant to be site-specific CV tables, while EML-mandated CV tables are prefixed "EML".

I had this SQL script to rename things in lter_metabase according to Gastil's doc on conventions. Was waiting for pull requests to be accepted and then incorporate these:

ALTER TABLE IF EXISTS lter_metabase."DataSet" RENAME TO lter_metabase."DataSets";

ALTER TABLE IF EXISTS lter_metabase."DataSetAttributes" RENAME TO lter_metabase."Attributes";

ALTER TABLE IF EXISTS lter_metabase."DataSetEntities" RENAME TO lter_metabase."Entities";

ALTER TABLE IF EXISTS lter_metabase."DataSetMissingCodes" RENAME TO lter_metabase."AttributeMissingCodes";

ALTER TABLE IF EXISTS lter_metabase."EMLAttributeCodeDefinition" RENAME TO lter_metabase."AttributeCodeDefinitions";

ALTER TABLE IF EXISTS lter_metabase."Peopleidentification" RENAME TO lter_metabase."PeopleIDList";

ALTER TABLE IF EXISTS lter_metabase."People" RENAME TO lter_metabase."PeopleList";

ALTER TABLE IF EXISTS lter_metabase."EMLMissingCodes" RENAME TO lter_metabase."MissingCodeList";

ALTER TABLE IF EXISTS lter_metabase."Keywords" RENAME TO lter_metabase."KeywordList";

ALTER TABLE IF EXISTS lter_metabase."KeywordThesaurus" RENAME TO lter_metabase."KeywordThesaurusList";

ALTER TABLE IF EXISTS lter_metabase."EMLUnitDictionary" RENAME TO lter_metabase."UnitList";

ALTER TABLE IF EXISTS lter_metabase."MeasurementScaleDomains" RENAME TO lter_metabase."EMLMeasurementScaleDomains";

ALTER TABLE IF EXISTS lter_metabase."EMLKeywordTypeList" RENAME TO lter_metabase."EMLKeywordTypes";

ALTER TABLE IF EXISTS lter_metabase."EMLMeasurementScaleList" RENAME TO lter_metabase."EMLMeasurementScales";

ALTER TABLE IF EXISTS lter_metabase."EMLNumberTypeList" RENAME TO lter_metabase."EMLNumberTypes";

ALTER TABLE IF EXISTS lter_metabase."EMLStorageTypeList" RENAME TO lter_metabase."EMLStorageTypes";

from lter-core-metabase.

Hi An, I need to apologize for not communicating clearly. I am sorry to cause you that extra work. I appreciate how you jump in and code. But maybe do not code right away just based on something Margaret and I write in these comments. I'm so sorry! We meant for our notes to be just reminders of ideas, a starting point for a discussion, and to record some observations. We had not reached any decisions, nor would we without input from you and the others. The git issue was a task to begin the discussion.

However, it's good you wrote all those renames because they show that what we wrote was not clear. Best way to test clarity is to hear it repeated back, as you have done for us here. Those are not the way we would rename, if we rename.

We have yet to decide if, or to what extent, we will retain the GCE naming patterns. If we do retain them, then we could make them more consistent. A few of your renames do that.

There is an advantage to having table names sort alphabetically and that also be a logical sort order for humans to manually edit db tables. For example, having "DataSet" at the start of table names makes those tables which contain the most common revisions of metadata to pre-existing datasets all list together. (Thank you to Li for pointing that out.) I do not know which naming convention suggested to you the removal of "DataSet" from tables like "DataSetEntities". If it was about matching view column names to EML elements and attributes, then I was not clear enough about that. Only views are seen by the EML-generating code. We are likely to align column names in tables, but there is no requirement there. Even so, the EML elements are not plural, so that could not be it entirely.

Or, maybe at some point we will decide even tables need to match EML names, where possible. That could help with code for INSERT and UPDATE, since those go to tables, not views. Currently the Perl code written to update mb tables has a separate utility script to refresh its mapping between db objects and code objects, to catch any renames or other changes to the db that the code has to know about. So the Perl code does not need editing when we change the db. Margaret and I do love our abstraction layers, it is true. :)

Possibly we, as a group, may decide to move away from the CapitalLetterTableNames and move toward all lower case with underscores like in pkg_mgmt. Or not. We may make more use of the prefix codes similar to cv_ and vw_, perhaps even ds_thing in place of DataSetThing. Or not. And Li suggested today maybe we should use vc_ instead of cv_ so that those would all sort to the bottom. But these are all only suggestions. Wait til we decide before renaming.

Note the above notes are also just notes, not directions.

from lter-core-metabase.

Quick answer to

I do not know which naming convention suggested to you the removal of "DataSet" from tables like "DataSetEntities".

If my memory serves the inspiration is from this passage in the naming doc @gastil wrote. I thought it made sense and shortens things a lot, but Li's point about sorting tables together is also valid. I could go either way.

​S​copeThing
means that thing at that scope (or block, or level) of the EML
I suggest that since an attribute always belongs to an entity, and an entity always belongs to a dataset, that we shorten this pattern.
Instead of DataSetEntityAttributeEnumeration it would be AttributeEnumeration (this table is not currently named that)
So there would be tables that begin with DataSet, with Entity and with Attribute, not DataSetEntityAttributeThing.
(I'll discuss di,ei,ai below)

from lter-core-metabase.