Add custom sections and/or attribute for reference/lookup tables

[kelle]

We are building a tool to make it easier for astronomers to make their own databases and we're using felis to describe the schema. For other parts of our stack, we need a variable that contains a list of the reference tables. e.g.,

REFERENCE_TABLES = [
    "Publications",
    "Telescopes",
    "Instruments",
    "PhotometryFilters",
    "Versions",
    "Parameters",
    "Regimes",
    "CompanionList"
]

It seems like this information would best belong in the schema.yaml. Would it be possible to add a custom section to the felis schema YAML specification so we can add some extra bits? or add an attribute that indicates if a table is a lookup/reference table?

[JeremyMcCormick]

Can you describe a bit more about what you mean by a "reference table?" These are tables that would be shared across all the user databases? Are they defined within the schema or external to it?

The two options which seem reasonable for adding this would be:

• Having a boolean flag attribute on the table objects in the YAML like reference: True if they are defined in the schema (We would probably need a better name for the attribute though as this seems vague.).

• Including a list of table names on the schema itself under a referenceTables key or similar if they are external (Again, we should cogitate on what it would be called if we went with this option.).

Please let me know if you prefer one or the other and what names you think would be clearest. It would be straightforward to add either option to the core data model. The boolean flag is the easiest. (It would just be a one-line additional to Table data model.)

[kelle]

Thanks for the quick reply!

The tables are defined within the schema and used within the individual databases. Each database instance could have a different set of reference tables. Every "data" table is linked to an astronomical source while the entries in the "reference" tables are supporting information like DOIs for the publications, longer descriptions of telescope and instruments, list of spectral regimes. (Here's an example schema ERD: https://github.com/astrodbtoolkit/astrodb-template-db/blob/main/docs/figures/schema_erd.png)

I agree that the boolean flag seems to make the most sense -- that's my first choice!

We struggle about what to call these tables. I believe they are technically "reference tables" but I find that label ambiguous because we also keep track of references in the DOI/publication sense. I prefer to call these tables "lookup" tables.

So, i would propose an optional table attribute:
lookup_table: True

[dr-rodriguez]

Just to add to this, the nomenclature I'll probably change, but is what we had adopted when using the AstroDBKit package (https://astrodbkit.readthedocs.io/en/latest/#). We've been internally referring to them as lookup tables, but the variable names were REFERENCE_TABLES. They are, as Kelle describes, lookup tables that help capture many-to-many relationships between rows in the database. The goal of AstroDBKit was to help define a more object-oriented SQL database, but we needed this concept to store things that are not intrinsically associated with any one source (like filter names, telescopes, publications, etc).

[kelle]

I have a reply to this thread in my email inbox that's not showing up here. I'll respond here nonetheless:

Yes, having a un-validated labels list would work for us. We could use label: ["lookup"]

[JeremyMcCormick]

I have a reply to this thread in my email inbox that's not showing up here. I'll respond here nonetheless:

Yes, having a un-validated labels list would work for us. We could use label: ["lookup"]

Apologies, I deleted a comment and meant to come back to it later.

I like the labels idea either as a list of strings or a dict of string key-value pairs; or it could be called annotations.

Something like one of these could cover a number of different use cases for labeling or annotating the objects with arbitrary extra information without need for a freeform, unvalidated section in the model.

[kelle]

I like the dict idea a bit better because I think it will be more readable. Eg,

Labels: {"lookup" : True}