Loading ...
Sorry, an error occurred while loading the content.

CWSUB layout

Expand Messages
  • Tangotiger
    Here s what I use: CREATE TABLE subs ( GAME_ID VARCHAR2(12) ,INN_CT INTEGER ,BAT_HOME_ID INTEGER ,SUB_ID VARCHAR2(8) ,SUB_HOME_ID INTEGER ,SUB_LINEUP_ID
    Message 1 of 9 , Apr 13 3:57 AM
    View Source
    • 0 Attachment
      Here's what I use:
      CREATE TABLE subs (
      GAME_ID VARCHAR2(12)
      ,INN_CT INTEGER
      ,BAT_HOME_ID INTEGER
      ,SUB_ID VARCHAR2(8)
      ,SUB_HOME_ID INTEGER
      ,SUB_LINEUP_ID INTEGER
      ,SUB_FLD_CD INTEGER
      ,REMOVED_ID VARCHAR2(8)
      ,REMOVED_FLD_CD INTEGER
      ,EVENT_ID INTEGER
      )
      ;


      When I look at it now, I think that all the bat_id, pit_id, fld_id,
      sub_id, run_id, etc should all have been something like bat_player_id,
      pit_player_id, etc.

      Tom
    • Tangotiger
      The one where Brian and I differ greatly is on the BAT_HOME_ID . I think he used something like LAST_BAT_ID . The former represents who is home and who is
      Message 2 of 9 , Apr 13 6:29 AM
      View Source
      • 0 Attachment
        The one where Brian and I differ greatly is on the "BAT_HOME_ID". I think
        he used something like "LAST_BAT_ID". The former represents who is home
        and who is away. The latter represents who is batting last and who is
        not.

        I would presume that Ted's program follows the definition for the former,
        not the latter.

        Brian and I have a slight disagreement for the "removed" fields. I have a
        slight preference for mine, if only because it's shorter and is just as
        clear. Like I said though, I'd be open to revamp all player IDs so that
        the suffix is player_id.

        Tom
      • Theodore Turocy
        ... I can confirm that the third field (number 2) is the batting team. Recall that Retrosheet s convention is that 0 is the visiting team and 1 is the home
        Message 3 of 9 , Apr 13 6:55 AM
        View Source
        • 0 Attachment
          On Mon, Apr 13, 2009 at 8:29 AM, Tangotiger <tom@...> wrote:
          > The one where Brian and I differ greatly is on the "BAT_HOME_ID".  I think
          > he used something like "LAST_BAT_ID".  The former represents who is home
          > and who is away.  The latter represents who is batting last and who is
          > not.
          >
          > I would presume that Ted's program follows the definition for the former,
          > not the latter.

          I can confirm that the third field (number 2) is the batting team.
          Recall that Retrosheet's convention is that 0 is the visiting team and
          1 is the home team, irrespective of the order in which they bat.

          Ted
        • Brian L Cartwright
          ...
          Message 4 of 9 , Apr 13 9:23 AM
          View Source
          • 0 Attachment
            >Here's what I [Tango] use:
            >CREATE TABLE subs (
            >GAME_ID VARCHAR2(12)
            >,INN_CT INTEGER
            >,BAT_HOME_ID INTEGER
            >,SUB_ID VARCHAR2(8)
            >,SUB_HOME_ID INTEGER
            >,SUB_LINEUP_ID INTEGER
            >,SUB_FLD_CD INTEGER
            >,REMOVED_ID VARCHAR2(8)
            >,REMOVED_FLD_CD INTEGER
            >,EVENT_ID INTEGER
            <);

            In the 5 fields that I could ID tht also occurred in events, I used the same
            name. I the 5 new fields I tried to follow convention. The 'REMOVED' above
            are shorter, which is fine, but although 'SUB' is implied because it's the
            sub table, I was thinking globally to come up with a descriptive and unique
            filename across all tables. It followed the pattern of 'REMOVED_FOR_PH' or
            'REMOVED _FOR_PR'.

            Third field 'BAT_HOME_ID' indicates top or bottom of inning, following
            'GAME_ID' and 'INN_CT'.

            It's the fifth field that wasn't as obvious, I take it to indicate if the
            SUB played for the visiting '0' or home '1' team. I used 'BAT_LAST_ID' to
            indicate if the SUB's team batted last or not.

            In my output, I need to specify the team code for the SUB's team, and also
            for the opposing team. This was explicit in the events table with
            'BAT_TEAM_ID' and 'FLD_TEAM_ID' but here I have to link to the games table.

            Which leads to question: The event field that are yes/no char(1) are called
            'FL' in the field name, but other fields that have 0/1 int(1) are called
            'ID'. Why aren't these also flags, as 0 or 1 is just another way of
            expressing T or F, one of only two possible results.

            Brian
          • Tangotiger
            Comments interspersed... ... Yes, exactly what I was thinking. In order to follow a universal convention, we would have been better off to use player_id ,
            Message 5 of 9 , Apr 13 9:47 AM
            View Source
            • 0 Attachment
              Comments interspersed...

              > name. I the 5 new fields I tried to follow convention. The 'REMOVED' above
              > are shorter, which is fine, but although 'SUB' is implied because it's the
              > sub table, I was thinking globally to come up with a descriptive and
              > unique
              > filename across all tables. It followed the pattern of 'REMOVED_FOR_PH' or
              > 'REMOVED _FOR_PR'.

              Yes, exactly what I was thinking. In order to follow a universal
              convention, we would have been better off to use "player_id", and then
              everything runs off that.

              Until then, if we keep it "table-centric" where we can, then anything off
              the SUB table is obviously a sub, and so, no need to make it longer.


              >
              > Third field 'BAT_HOME_ID' indicates top or bottom of inning, following
              > 'GAME_ID' and 'INN_CT'.

              Ted said it indicates home team or visiting team, and not "top/bottom".
              99.99% of the time they are the same, but not always.

              > Which leads to question: The event field that are yes/no char(1) are
              > called
              > 'FL' in the field name, but other fields that have 0/1 int(1) are called
              > 'ID'. Why aren't these also flags, as 0 or 1 is just another way of
              > expressing T or F, one of only two possible results.
              >

              If they are true binary, then they should be switches (FL, flag). But, if
              they identify something, then they are ID. The TEAM_ID is a way to assign
              an identifier to each of the two teams, rather than an "existence /
              absence" to one, and the opposite to the other. I don't know what
              "BAT_TEAM_FL" = 0 means, but I do know that "BAT_TEAM_ID" = 0 means.

              So, it's not that you have two possible choices, but that you have a true
              "exist / not exist" case.

              Tom
            Your message has been successfully submitted and would be delivered to recipients shortly.