Unless you want to show the stat lines for a player like Rob Ducey or Matt Luke who had multiple stints with a single team.

sean
---
Sean Forman
Sports Reference LLC, President
http://www.sports-reference.com/

On Mon, Mar 19, 2012 at 12:09 PM, Mike Emeigh <mwe55innc@...> wrote:

 

I agree that stint data should be in a separate table - it's not going to vary from, say, batting to pitching to fielding, I wouldn't think.

Where it's known, it might also be useful to have a start date and end date for each stint.

Sent from my iPhone

On Mar 19, 2012, at 11:55, "Tangotiger" <tom@...> wrote:

 

I agree with Kevin that it should appear in some other table.

Unless, well, do people need to track the batting line of someone who
played with the Mets in 1986 in Apr-May, with the Expos in Jun-Aug, then
again with the Mets in Sept 1986, so that the two 1986 Mets lines are
distinct? If you have an online DB like BR.com, or BaseballCube, or
Fangraphs, sure, maybe. For the rest of us though?

If ever we were to create a "splits" table, say, performance by home/away,
we wouldn't REALLY do a Apr-May Home split AND a Sept Home split, would
we? (I mean, if you wanted to do that, you'd want it for every player, so
you have a Home-month split, and you wouldn't want it only for those who
have two distinct splints.)

We've seen already that it's tripped up Lahman for the 2011 data, and it's
alot of extra effort to get it right (and based on the recent post, it's
still not right). (Not criticizing Sean, just pointing out that it trips
up even the most diligent of researchers.) Imagine the rest of us who
wouldn't necessarily always remember to link the stint IDs. (I fell in
the trap to this recently.)

So, I go back to "is it worth it"? Is it worth Sean's time to get it
right, is it worth our time to validate it, and is it worth our time to
first sum at the player-year-team level 99.99% of the time because we just
don't need that splint id?

Note: The splint-id existence predates my involved with the DB, so I'm
sure this has been covered many years ago.

Tom

Right, Mike has the spirit of my question.

When I ask if it's worth it, I'm not asking if it's useful.  Clearly, it
has many uses, be it the chrono-order of listing teams, or, for those who
need it, splitting the stints of guys who left a team and came back in the
same year.

I'm asking if the cost of that use is a justifiable cost.

We had bad data in the first release, and we have bad data according to
the recent post, and it's traced to the stint ID.

One alternative is as Kevin suggested, and simply having a separate table,
that lists the stints for the players.  It keeps it away from that
batting, pitching, fielding table.  And, you can even enhance it by
including dates.  So, this handles the chrono-order need that some have.

That leaves us with the guys who left a team and came back in the same
year, while playing for some other MLB team in the same year.  Rob Ducey
and whatever other players are similarly affected.  Do we need to see his
batting and fielding line split between stints?  If not, then a separate
STINTS table handles that as well.  Indeed, if you really really need it,
you can have a StintsBatting table as an offshoot of the Batting table.

Think of the StintsBatting table as a split table, just as you might do a
HomeawayBatting table, you have a StintsBatting table, and it'll be
comrpised of Rob Ducey and the other handful of players around.

Everyone gets what they want here.  Without the headache of having to
remember to sum the Batting, Pitching, Fielding tables.  And, without what
we've seen so far, of having to contend with bad data, because of the
time-cost associated with it.

Tom

Just for the sake of a survey, I also use the stints in the Batting, Fielding,
and Pitching tables for my website.  Not only to correctly order the teams in
each season, but to also correctly join the tables for players who played for
the same team in 2 different stints.

I may be biased, but changing the structure of the database may cause many of us
to have to change our existing code.

I don't imagine it would be too difficult to make a Master Stint table that (for
example) shows Mike MacDougal CHA 1, Mike MacDougal WAS 2 and then apply it to
Batting, Pitching, and Fielding tables.

Dan Hirsch

Another advantage of having a STINTS table is that you can incorporate
minor leagues as well.  If someone has a Mets-Expos-Mets needs for
same-year players, then surely someone else would have a
Boston-Pawtucket-Boston needs for same-year players.  Not that STINTS
table *must* do that, but it *can* be used to accomodate that requirement.
  Some guy gets sent down for three weeks, say JJ Hardy, then it might be
good to know when that happened.

And, as I said, a StintsBatting table will give the user what he needs, if
he needs it at that detail.

Basically, it's a question of doing a small redesign, and this will
localize any data quality issues in a very tight manner that will affect
only those people who need that data.

Tom

"Tangotiger" <tom@...> wrote:
> I'm asking if the cost of that use is a justifiable cost.
> We had bad data in the first release, and we have bad data
> according to the recent post, and it's traced to the stint ID.

The problem of bad data don't have anything to do with the database design. It's
100% attributable to me, the person who processed most of the updates. It's the
first time in several years that I made the offseason updates rather than Sean
Forman, and the scripts I used to make and check the updates were outdated. It
shouldn't be a problem in the future.

I think it's much more labor intensive to use and maintain a table that lists
start and end dates in a separate transaction file, especially if we continue to
maintain batting/pitching/fielding as separate files.

But I'm certainly open to further discussion, on this or other design issues.

Regards,
Sean Lahman

Paul,

Your post is clear why we do *not* want to keep the stints as-is.

The requirement about chrono-stints can already be addressed by a Stints
table that shows the stint order.  Several posters have already responded
positively to this.

The "batting" table's dual role has already caused problems in the past.
I think the official MLB position is that the "batting" table is the "all
appearances" table, so that any game gets recorded with a "batting"
record, even if he didn't bat.  (I'm not exactly sure about this, but I'm
going on memory here, but it's consistent with players not batting still
having a batting record.)

Anyway, from a database perspective, we don't need to have a stint denoted
in the batting *and* pitching *and* fielding tables, and ensure it
matches.  The key fields of tables are supposed to identify records in not
such a rigid way that you would have to alter the key field if you find
the data needs to be updated.  That's why playerID fields should never
change, even if a pitcher's name gets changed.  You don't want to have the
stint as a key field, if it means that it may change if we have new
information.  Imagine we introduce minor league data.  Now, you've got
MASSIVE changes in key fields for tons of players across multiple tables.
(Think of players like JJ Hardy.)

At least, with a Stints table, it will be localized to a single table,
whose entire purpose is to track that.  Indeed, you wouldn't even need to
have the stintID have to be a key field.

Had we started with a clean slate, the Stints would be treated
equivalently to Home/Away splits or Inning splits or Starter/Relief
splits.  They'd be part of a child table.

Tom



> My vote is to keep the stints as is. 
>
>
> From a database perspective, it is much, much easier to take a stint
> divided table and sum it up to get the overall numbers than it is to try
> to take a combined table and then split it back up using a separate stint
> table.  I suspect it would be harder for the administrator to maintain
> two separate tables.
>
> From a baseball perspective, the stint field in valuable to determine how
> a player moved from team to team during a season.  This is pretty basic
> information.  Does everyone need this information?  No.  Is it useful
> for people who do need this information?  Yes.
>
> Also, along with the team discrepancies on the stints that I noted to
> start this (unexpected) thread, there are also several hundred pitching
> stints in the years 2009-2011 that did not have a Batting record at all. 
> Almost all of them are in the AL and I suspect none of the pitchers
> involved ever batted.  This is not a huge deal, except that for all other
> seasons if a playerID had a Pitching record he always had a Batting
> record, even if he never batted.  You may already be aware of it at this
> point, but I mention it anyway.
>
> Paul Golba
>
>
>
> ________________________________
>  From: anson2995 <slahman@...>
> To: baseball-databank@yahoogroups.com
> Sent: Tuesday, March 20, 2012 9:39 AM
> Subject: [baseball-databank] Re: Stints
>
>
>  
> "Tangotiger" <tom@...> wrote:
>> I'm asking if the cost of that use is a justifiable cost.
>> We had bad data in the first release, and we have bad data
>> according to the recent post, and it's traced to the stint ID.
>
> The problem of bad data don't have anything to do with the database
> design. It's 100% attributable to me, the person who processed most of the
> updates. It's the first time in several years that I made the offseason
> updates rather than Sean Forman, and the scripts I used to make and check
> the updates were outdated. It shouldn't be a problem in the future.
>
> I think it's much more labor intensive to use and maintain a table that
> lists start and end dates in a separate transaction file, especially if we
> continue to maintain batting/pitching/fielding as separate files.
>
> But I'm certainly open to further discussion, on this or other design
> issues.
>
> Regards,
> Sean Lahman
>
>
>


---------------------------------------------
The Book--Playing The Percentages In Baseball
http://www.InsideTheBook.com

Does anyone have a comment on the reply below?

I'm always running into problems with appearances and have to use the fielding
table.  Not the best table to JOIN with since almost every player has multiple
records per season.

Thanks,
-Chris

--- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
>
The "batting" table's dual role has already caused problems in the past.
I think the official MLB position is that the "batting" table is the "all
appearances" table, so that any game gets recorded with a "batting"
record, even if he didn't bat. (I'm not exactly sure about this, but I'm
going on memory here, but it's consistent with players not batting still
having a batting record.)

There's an APPEARANCES table in the Lahman DB.  I haven't verified it, but
that might help you.

Tom

> Does anyone have a comment on the reply below?
>
> I'm always running into problems with appearances and have to use the
> fielding table.  Not the best table to JOIN with since almost every player
> has multiple records per season.
>
> Thanks,
> -Chris
>
> --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
>>
> The "batting" table's dual role has already caused problems in the past.
> I think the official MLB position is that the "batting" table is the "all
> appearances" table, so that any game gets recorded with a "batting"
> record, even if he didn't bat. (I'm not exactly sure about this, but I'm
> going on memory here, but it's consistent with players not batting still
> having a batting record.)
>
>


---------------------------------------------
The Book--Playing The Percentages In Baseball
http://www.InsideTheBook.com

I don't fully understand what is being proposed, but I just wanted to
take a moment to speak out in favor of backwards compatibility.

For example, people bought "Puresim 4" ( a baseball simulation game by
Shaun Sullivan). It loads data in the "Lahman" format. Even though the
game was published in 2010, it can still load last year's database and
this year's database. If the stint column goes away, many users will be
unable to load next year's database.

If it's too difficult to ensure that the stint data is correct, I'd
rather see it 90% correct, but with documentation that (for example)
stint data after 2010 may not be 100% accurate.

Clay

BDB or Lahman DB would be "backward compatible" with the proposed change,
or at least have that as a potential.

If we do this with a STINTS table that shows it the way we've been talking
about it, then you could join the Batting, Pitching, Fielding table to the
STINTS table, and get the stintID for virtually every record.  There's no
reason that it has to be "backward compatible" out of the box.  But, one
extra join on each table will make it backward compatible.

The only issue will be the Rob Ducey's of the world, who leave and come
back to the same team in the same year, while interrupted by a different
MLB team.  Again, that can also be handled with a StintSplits table,
similar to have a splits table for Batting v Pitch Hand if we wanted to
eventually go there.

Anyway, this is really in Lahman's hands, as the keeper of the DB.  He
says that the data quality issues was that he needed to change his script.
  If that's the case, that we won't experience data quality issue again as
a result of stints, then, fine, the issue is mostly moot.  Those of us who
have no need to the stintID field can do the appropriate matching and
summing after-the-fact.

I will say that once you incorporate minor league data, we're going to
revisit this all over again.  As it currently stands, we're going to have
massive key changes when that happens.  JJ Hardy getting sent down to the
minors and being called up will have stintID 1 and 3 in 2009, whereas
right now, he has only 1 record.  And it'll get worse for guys that get
sent down and called up multiple times in the same year.  But, we're not
there yet.

Tom

Retrosheet changed their minds and will be using TeamID MIA - I think we should
do the same...

THANKS,
Kevin

--- In baseball-databank@yahoogroups.com, Matthew Gargano <mgargano@...> wrote:
>
> Using standard database protocol, franchise ID should *never* change.
> Regardless of where the team moves.
>
> On Thu, Mar 1, 2012 at 9:53 AM, Sean Lahman <sl@...> wrote:
>
> >
> >
> > My plan is to use "MIA" as the teamID for the Miami Marlins. Don't think
> > BBRef or Retrosheet have weighed in yet.
> >
> > As far as the FranchiseID, my inclination is to leave it as is for now.
> >
> > Regards,
> > Sean
> >
> > ---
> > Sean Lahman
> > http://seanlahman.com
> >
> >
> >
> >
> >
> >
>

I didn't see a response on this... Will DH no longer be included in the fielding
table?  Thanks.

-Chris

--- In baseball-databank@yahoogroups.com, John Rickert <rickert@...> wrote:
>
> In the Fielding.csv file previous seasons have totals for games played at DH,
but 2011 does not.
>

Dear group members,

Please excuse the cross posting. When examining more than one season's
worth of data, do you think that there is a need to examine the data
using time-series methods?

If this is too far off topic for the general group discussion, please
feel free to respond to me directly.

Regards,

Nick Miceli

Being new to databases, I am wondering if someone might help me with
instructions on how to query career stats from the csv data provided recently.

I have tried opening the db file itself, but my computer locks up (as if the
file is too large to process. Is there a trick to this I don't know of?

All help is greatly appreciated.

You should download the Lahman MS Access database:

http://www.seanlahman.com/baseball-archive/statistics/

It'll make life easier for you.

Tom

> Being new to databases, I am wondering if someone might help me with
> instructions on how to query career stats from the csv data provided
> recently.
>
> I have tried opening the db file itself, but my computer locks up (as if
> the file is too large to process. Is there a trick to this I don't know
> of?
>
> All help is greatly appreciated.
>
>
>


---------------------------------------------
The Book--Playing The Percentages In Baseball
http://www.InsideTheBook.com

Hi there,

I would like to merge (unite, join or however it's called) different databases.

We have a baseball site to highlight stats of latino players and we have found
some databases that we'd like to work with. We downloaded the mlb and npb
databases. And we're working on a venezuelan baseball, italian and dutch 
databases too.

But the problem are the id's of players, teams, managers, etc. The question is:
Is there a way to create one "main", batting, pitching, etc. table other than
doing it by hand?

I'm sorry if you don't understand the question, you sure can tell that english
is not my mother language.

Thanks in advanced for any tips.

Greetings,