Normalizing the Scoreboard, Part I

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.