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.

This entry was posted in tech. Bookmark the permalink.

2 Responses to Normalizing the Scoreboard, Part I

  1. I work for akamai now, though. So maybe it’s not an issue :)

  2. dbt says:

    Sure, but there are still historical entries to worry about. :)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>