52 Days: 1st Weekend in the weeds episode: where do I put my data? Facility and (the start of) key related tables
For the weekends, we’ll be head off into the weeds for (brief) discussion of database considerations…
A few options and thoughts about key database tables, their relationships to each other, and key fields for each
(This debrief took a large automotive client 3 months of all day every day meetings, so this will only be the very highest of highlights. If you are interested in more detailed discussions, please reach out)
What? You keep derived data in your database?
Before we get into the “nuts and bolts” about database tables fields, first a few words about keeping derived data in the database table (not considered ideal by computer science purists).
In the course of your calculations about Tier2 reporting requirements, you will probably make a lot of assumptions, edit data extensively, and re-derive different pieces of input data on your way to arriving at your final Tier2 report.
Spring Hill Manufacturing, formerly Saturn Spring Hill. A single facility for purposes of EPCRA reporting because plants are adjacent and under the same ownership
While in a perfect world these would theoretically be recalculated from the raw data each time you want to review the results, from a practical standpoint, it would be pretty easy to make a mistake and lose that valuable data you spent so much time determining.
And if the data was from previous years, you may not even know how the data was derived. My understanding is that Data Warehousing aps now routinely maintain derived data, so that it can be accessed swiftly for purposes of the hugely powerful discipline of Business Intelligence BI
If you follow along with my examples through the rest of this reporting period, at the end of the process you will have a (fairly) thick notebook, either virtual or physical (or both), that takes anyone who wants to follow your logic step by step through every assumption made and every value determined.
There will be tables for the “raw”, unchanged data. We will present the logic to go from that raw data to all subsequent derived values.
Get ready to dive in and have some fun (if you’re a geek like me). If you are, my condolences, but you’ll probably always have job security because most people don’t want to take on this level of detail.
Facility table: The unique physical entity for Tier2 and Toxics Release Inventory TRI reports
High-level table independent of reporting year or reporting period or other attributes. Useful to have a unique identifier that is human recognizable, such as “Spring Hill” in the picture below. Note that the location won’t change even when ownership changes (and the EPA numbers go with the physical facility).
This table can contain attributes that there are only “one of”, such as Tier2 ID Number, Toxics Release Inventory TRI ID Number. This can also be the table used to print/export key information onto the Tier2, TRI or other reports.
Key information/ fields may include: Street Address, County, Mailing Address, Latitude/Longitude, Owner/Operator info, Technical and Emergency Contact Person information, Primary SIC Code, Dun & Bradstreet Number, Report Signer info for both the Tier2 and TRI reports, and other fields that print/export to the Tier2 or TRI report including Underground Injection Code UIC 1 and 2, Federal Employee ID Number FEIN, Parent Company and related information
A Facility/History table may be useful, copy the information off to “archive” it when the information changes. There will probably be lots of other Facility subtables (we certainly had many, only a few are highlighted here).
Facility/SDS: Table that maintains which Safety Data Sheets SDS’s are approved for use at a particular Facility irrespective of whether that SDS was actually on site during a particular reporting period.
Facility/SDS/Yr: So named because it contains derived summary information for each SDS specific to the Tier2 Reporting Year. You may also have a Facility/Reporting Period table that is related to Regulations/Lists of Interests (RegLOI), which may have reporting periods different than yearly.
This is the main working table for keeping whether and why this SDS was Tier2 reportable (Reportable by Amount, meaning more than 10,000 pounds on-site per year, or Reportable by Extremely Hazardous Substance EHS, reportable because the SDS contained an EHS that was reportable, Reportable by Trade Secret, and Reportable for Other Reason are the 4 we maintain. A chemical can be more than one of them.)
We also used to to maintain the sums of the various inputs used for threshold determinations: Purchases entered manually, purchases processed in electronically, physical inventory starting and ending summary values, etc..
It maintains reasons the material was not reportable even if it exceeded a threshold, such as which exemption applied.
This table has LOTS of fields, the more Tier2 work you do, the more you will find reasons to capture your thoughts about this particular SDS with respect to Tier2 reporting
Facility/SDS/Location/Yr: This table maintains all the locations that were included in Tier2 calculations. This is the “raw data” table from which the Facility/SDS/Yr values are derived.
If you would like to have a longer discussion about database structure, feel free to reach out to me.
Community outreach: What database structure do you use to capture this data? What are key fields in some of the tables?
#SARA312 #SARATierII #SARATier2 #EPCRA #EGLE #March1EPAReportDeadline #Tier2Training
Michigan EGLE links from first and second webinars of 2022 SARA 312 virtual training courses:
I’m keeping these links for awhile because they are so useful.
Here’s the link to the second webinar presented on Wed Jan 19, 2022. Really good example on batteries. This is the “in the weeds” how to conduct your threshold determinations episode:
RECORDING LINK FOR SESSION 2: https://attendee.gotowebinar.com/recording/4027906237723673347
Also, I talked to Mike Young, one of the presenters, and he said that it’s not unusual for them to take phone calls from people from other states (with the warning to confirm that your state doesn’t have differences in their requirements compared to Michigan)
RECORDING LINK FOR SESSION 1: https://attendee.gotowebinar.com/recording/7957790593170499843.
PRESENTATION: (I wasn’t able to attach a copy of the presentation, email the link below and they will send it to you)
SARA TITLE III – TIER II REPORTING WEBSITE: http://www.michigan.gov/sara
MICHIGAN FACILITIES’ GUIDE TO SARA TITLE III: https://www.michigan.gov/documents/deq/deq-oea-saraguidebook_509720_7.pdf
CONTACT INFORMATION:
Mike Young and Adam Pohl
517-284-7272
