An experienced software engineer described a database design pattern to me in which there are three kinds of tables: transactional tables, associative tables, and data definition/taxonomy tables.
As he described it, a transactional table might store information about a user, product, etc. For example, the USER table might have columns id, lastname, firstname, username, password, etc. The PRODUCT table might have columns id, description, price, etc.
An associative table is used to create many-to-many relationships between other tables. An example would be a table for keeping track of items in each user's shopping cart. It might have columns product_id and user_id.
A data definition table contains information about the definition of columns in other tables. For example, there might be a table to that lists password hashing algorithms used over the lifetime of the database and the USER table might have an additional column like password_definition that points to this table. In this way, we don't end up adding columns like password_md5, password_sha256, password_hmac, etc.
My example is contrived just to help describe this pattern. I'm not looking for advice about the example, but information about the design pattern illustrated and specifically about using a table to describe data in another table.
Does this kind of database design have a name? Are there better terms for these types of tables? Are there established best practices for when it should or should not be used?