Newb SQL question: Is there a "foreign key" equivalent for referencing columns, or is it logical to store column names as strings in another table?

Question

Disclaimer: I'm an SQL newb, just discovering the great possibilities of sqlite embedded in applications.

I'm designing a small database for an application that primarily handles data storage for plotting. Say I have several instrument data tables (one for each type of instrument) with a column for each data field, such as pressure, temperature, humidity, etc. I would like to join these data tables, pull out the column names, and assign a more human-readable column "description" made available to the user for selecting the independent/dependent variables for plotting. Getting the column names in sqlite is easy enough, but I'm not sure if I should store the resulting names (and longer descriptions) as another table in the db or in a list of some sort in the application. If it were another table in the db, I think I would have to store the column name as a string... unless there is some way to reference a column in SQL? I'm not sure if there is a foreign key equivalent for columns, or if this is just a sign of bad database design needing restructuring.

Thanks for any suggestions, stack overflowers.

Answer 1

There are ways to do introspection in SQL and pull out table and column names, but you probably don't want to do it that way, and the implementation details of that are highly dependent on your particular RDBMS.

Since the human-readable column name is a display detail, and likely a static value, keep it out of the database and somewhere in the display/rendering portion of your app. (There are exceptions to this, such as if you want the column name to be customizable per-user.) You can keep a hash or dictionary which defines a human-friendly name and uses the actual database column name as its key. This setup can be handy for saving other settings for the display of the column, such as datatype, precision to display (if a number), etc.

Answer 2

What you are after is sometimes called a 'data dictionary'. If the DBMS does not provide annotation facilities, then you will need to simulate them, and that is likely to include creating a table with table names as well as column names in it. It is perfectly reasonable to do that.

Be aware of the difficulties with keeping track of changes to table names and added or deleted columns; the built-in facilities only keep track of the data in the system catalog (the 'data dictionary' information that the DBMS needs to track what goes on) and will not keep track of what is in your table.