Right now, the scoreboard is simply a table: name, employer, start, end, reasonleft. The latter two can be null. the first three shouldn’t be.
On top of that, there’s a huge ass view definition that creates all the accessory fields. Score, tenure, milliroma, etc.
Now, the view is ok if a little unwieldy. But the input data kinda sucks. However, normalizing it would take four steps, and the last two suck.
The first is easy. Person table:
personid, first, last
new jobs table:
personid, employer, start, end, reason
The second is also easy and obvious:
new employer table:
companyid, companyname
new jobs table:
personid, companyid, start, end, reason
However.
This is where normalization gets complicated. rlh got laid off from Sun. However, she initially took a job for Cobalt. Change the Sun companyname to Sun/Cobalt? Nope, fluffy never worked for Cobalt.
Second problem:
spruance quit CCSO. He now works for CITES. jrr started working for CSO, then CCSO, now CITES. All the same org, name keeps changing.
Solution:
company table:
company_id, name, acquired_by_id, acquired_date
company_names table:
company_id, name, start_date, end_date
so now:
person->job->company->company_names
and company has to self-join to find acquirers.
This still doesn’t cover people like heather whose job was sold to another company that stephen works for but chris still works for the original. Sigh.
