Clojurians Log v2
sql
Bit of an open ended question: does anyone have recommendations for how to lean on SQL to create what might be called "self-describing databases?" The stuff most SQL implementations expose via the information_schema views provides some rudimentary metadata, but because these are system-level views rather than real tables, you can't do things like add a description column to information_schema.tables. Usually, this stuff is provided in a data dictionary or documentation outside the DB itself, but having some of it live in the DB seems like it could be very useful - I've begun recording table-level metadata in a metadata schema to keep it "closer to the data" in a queryable form.
Going too far with this idea seems like another iteration of the https://en.m.wikipedia.org/wiki/Inner-platform_effect; I'm looking to augment rather than replicate the system-level views. Does anyone have resources or recommendations on this?
It still makes enforcing relations harder in the metadata context; stuff like "The table_name col in the load_history table needs to reference an existing primary key in the table_info table" is not easy to jam into an EAV table
Yea but there are benefits like being able to add new attributes at runtime. So might be worth it in some cases.
I've done this where I work. We needed to add attributes to tables, table columns, and table columns in a specific context (e.g., list view, search controls, single record preview, etc). It is nice for semi-technical internal users. The thing that becomes harder is deployments, because now you need to sync not only code, but data as well. When I get a chance, I plan to make it so all of the data can be synced to and from text files (e.g., toml), and run that as a deployment step for at least some of the data if it can be maintained in text.
Interesting that the article you pointed to uses EAV as an example of an inner platform effect. We didn't use EAV, but it isn't obvious to me that it would be a bad idea in this context.
I might be missing what you're trying to do but some databases allow you to add text as metadata to DDL objects. You don't mention which database you're using but PostgreSQL allows you to add COMMENTs to database objects (schemas, tables, columns etc) using syntax like this:
COMMENT ON TABLE <schema-name.table-name> IS 'This is a comment on a table.';
COMMENT ON COLUMN <schema-name.table-name.column-name> IS 'This is a comment on a column.';Unfortunately, the COMMENT ON TABLE feature isn't supported in my target dialect (https://duckdb.org/docs/archive/0.9.2/sql/introduction). So I'll need to reify it using a specific set of tables, which ultimately allows for recording metadata in a more structured way anyway.
If I need support for arbitrary k/v data that doesn't have a consistent schema, I can rely on a column with the MAP https://duckdb.org/docs/archive/0.9.2/sql/data_types/map to record it for any rows that need it.