Building an ESG Metrics Database for US Law Firms: ESG Intelligence Group's Data Management Solution

I built a database for ESG Intelligence (ESGi) Group, a consulting firm that advises law firms on implementing good environmental, social, and governance (ESG) practices internally as well as how law firms can better serve their clients with respect to ESG. This paper explores my decision-making process for the design of the database and the challenges I ran into while creating and populating the database. I turned a list of things that ESGi Group wanted to track into an entity relationship diagram (ERD), which I eventually turned into a relational database in MySQL. I further defined the contents of the database by mapping the ERD into a relational model, normalizing the relational model, and creating an attribute domain table. I coded the database in SQL, collected data in an excel spreadsheet (downloaded from AMLAW 200 and NLJ 500 purchased data, manually searching individual firm websites, and scraping law.com in R), and then inserted the data into the database. I ran into issues with data completeness due to the lacking regulation of firm transparency about ESG reporting, but this project succeeded in proof of concept rather than implementation. I also discuss security and privacy considerations, and ESGi Group’s possible options for further development of this project in the future.