Do any of you folks have something in your apps that you'd describe as a data dictionary? I mean some sort of (ideally searchable) reference to all the tables and columns (maybe more like objects and fields) in the app, their user-friendly names, and which ones are required, or may be in some situations? I don't mean information_schema, since in general that doesn't know about friendly names or required-ness, and doesn't have the concept of tables that in some sense are "child" objects under other ones. If you have such a thing, is its data in the db, or in code? If it's in the db, how did it get there?

If I'm understanding, I've worked at places that had a "utility" table where we mapped human readable names to ids and then had a slug version as well. We'd cache most of this data in a service

When we did "The Big Rewrite(tm)" we started out by writing a domain dictionary -- that talked about all the entities in our system and their attributes (and some associated functionality), and then then we developed that (wiki page) into a list of tables and relationships, with their main columns (both actual names and "friendly names"). But once the system was up and running in MVP form, that fell out of use -- as pretty much all our wiki pages have over time -- and the closest we have to any up-to-date equivalent is spread across all the Jira tickets that caused changes to existing tables or the addition of new tables...

We have XML files that map our database to our Java GUI. And we have a report that does an XML Transform on those XML files into a spreadsheet report for users.

I use a series of several database tables in a master "database" that has all my various config code values by reference. I use mariaDB. I use HeidiSQL mostly for UI. I store table names, database names, database source names, expected input type, expected values or range of values, Active Status, Notes, where its running ON, when it was lasted worked on and app configs. it makes life easy when all you can remember is some name of some app, and not much else to go on.

Thanks folks, interesting spread of practices. For those of you who do have this sort of info in a db, does your code reference that data to run, or is the db data a separate island for reference only?

In the past when I did it, we'd use these definitions to drive drop downs and such so if we had code that let's say wanted to check if an order was a specific type, we'd replace this

// comment reminding poor devs who come behind what the guid means
if( order.getType() eq 'guid-here-from-the-db' )

with something like this

if( order.getType() eq UtilityService.ORDER_TYPES.ONLINE )

And when we built the HTML out for dropdowns or radio buttons, etc, we'd have a method to get all the items for that type (usually back as a query)

qryOrderTypes = UtilityService.getGroup( 'ORDER_TYPES' )

@Dave Merrill Yes, in the application startup, we loop through the application.config variables, which are stored in a table. The table contains ID, ApplicationVarName, ApplicationVarValue, IsActive, and Environment. Beyond a setting the initial application.DSN qoery, you can use this to change from dev to production or both based upon critiera you set.

So I contain and create the data dictionary in the database. I am on MS SQL Server and use the Extended Properties MS_Description on the tables and fields. And then I have a SQL Script that goes through and gets all the schema.tables.fields and extended properties and creates an output.

@Michael Schmidt What do you do with that output? Make it available on your intranet? Does your actual code use that info at all?

I have the output available in an area for just me in the app ... but I also place it in a GitRepo of markdown that I use to create my SDD