I have a database of 1,050 tables with unique alphanumeric names like
B20017F, each with columns named
B02001_003, and so on. I’m trying to create a way to search for tables by their actual subjects and titles.
For instance if table
B07009‘s subject is
Educational Attainment and the columns
High School Diploma,
Bachelor's Degree or Higher and so on, how can I create a way to search the database for tables matching “education” or “profession” or “commute time” and get the columns that I need? Some of the tables have over 100 columns with names that exceed 64 bytes which is why I can’t simply name the tables and columns with their descriptive names.
I’ve tried creating a table called
labels but some tables have hundreds of columns (like the Professions table) and exceed the postgres row size limit of 8192 bytes. I’ve also considered storing the lables for every table and column in a json file and searching it with python before executing SQL statements. What is the best way to attack this problem?