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

RE: [baseball-databank] Digest Number 922

Expand Messages
  • Derek Adair
    On a related note, since you brought up table structure: I ve always felt that Brooklyn and Dodgers (location and team nickname) should be separate fields
    Message 1 of 6 , Jun 26, 2007
    • 0 Attachment
      On a related note, since you brought up table structure:

      I've always felt that "Brooklyn" and "Dodgers" (location and team
      nickname) should be separate fields in the database, and I separate them
      as such when I import.

      That helps out quite a big when doing some of the queries mentioned (show
      me all the Washington teams, for example).

      Regards,
      Derek

      On Tue, 26 Jun 2007, Ben Baumer wrote:

      > I think Tom is right. Why not add another layer of information if it
      > adds something and doesn't take anything away? It wouldn't be that hard
      > to populate.
      >
      > On a slightly different note, I think that there are other ways to
      > improve the structure of the implementation of "teams". For example,
      > there are 149 distinct teamIDs, but only 120 distinct franchIDs. As far
      > as I can tell, there is no lookup table for each teamID, so for example,
      > there is no way to generate a list of teams that includes the string
      > "Brooklyn Dodgers". Why should you be able to generate a similar list
      > with the phrase "Los Angeles Dodgers"?
      >
      > In my view, and I think in terms of 3NF design, instead of the two
      > tables "teams" and "teamsfranchises", there should be three tables:
      > "teams", "teams_lkup", and "franch_lkup". The first would be the same as
      > it is now. The second would have one and only one entry for each
      > distinct teamID that appears in "teams", and have entries like:
      >
      > teamID teamName firstYear lastYear lgID active
      > BRO Brooklyn Dodgers 1890 1957 NL N
      > LAD Los Angeles Dodgers 1958 NULL NL Y
      > .
      > .
      > .
      >
      > Then, the "franch_lkup" table would have a unique key for each franchID
      > that appears in "teams", so it would look like this:
      >
      > franchID franchName
      > LAD The Dodgers
      >
      > or whatever you want to put in there.
      >
      > Isn't this more logical than the current setup? And doesn't it improve
      > functionality? For example, you could now do:
      >
      > SELECT teamName, sum(g) as G FROM teams t LEFT JOIN teams_lkup d USING
      > (teamID) WHERE t.franchID='LAD' GROUP BY t.teamid;
      >
      > and get:
      >
      > teamName G
      > BR3* 783
      > Brooklyn Dodgers 10752
      > Los Angeles Dodgers 7788
      >
      > * See, I don't even know who BR3 is? You should be able to recover the
      > name of that team!
      >
      > -Ben
      >
      >
      > http://www.baseball-databank.org/
      > Yahoo! Groups Links
      >
      >
      >
      >
    • robert bluestein
      but they were also the Bridegrooms and the Robins at one time. Should we break that up too? Derek Adair wrote: On a related note,
      Message 2 of 6 , Jun 26, 2007
      • 0 Attachment
        but they were also the Bridegrooms and the Robins at one time. Should we break that up too?

        Derek Adair <dadair@...> wrote:
        On a related note, since you brought up table structure:

        I've always felt that "Brooklyn" and "Dodgers" (location and team
        nickname) should be separate fields in the database, and I separate them
        as such when I import.

        That helps out quite a big when doing some of the queries mentioned (show
        me all the Washington teams, for example).

        Regards,
        Derek

        On Tue, 26 Jun 2007, Ben Baumer wrote:

        > I think Tom is right. Why not add another layer of information if it
        > adds something and doesn't take anything away? It wouldn't be that hard
        > to populate.
        >
        > On a slightly different note, I think that there are other ways to
        > improve the structure of the implementation of "teams". For example,
        > there are 149 distinct teamIDs, but only 120 distinct franchIDs. As far
        > as I can tell, there is no lookup table for each teamID, so for example,
        > there is no way to generate a list of teams that includes the string
        > "Brooklyn Dodgers". Why should you be able to generate a similar list
        > with the phrase "Los Angeles Dodgers"?
        >
        > In my view, and I think in terms of 3NF design, instead of the two
        > tables "teams" and "teamsfranchises" , there should be three tables:
        > "teams", "teams_lkup" , and "franch_lkup" . The first would be the same as
        > it is now. The second would have one and only one entry for each
        > distinct teamID that appears in "teams", and have entries like:
        >
        > teamID teamName firstYear lastYear lgID active
        > BRO Brooklyn Dodgers 1890 1957 NL N
        > LAD Los Angeles Dodgers 1958 NULL NL Y
        > .
        > .
        > .
        >
        > Then, the "franch_lkup" table would have a unique key for each franchID
        > that appears in "teams", so it would look like this:
        >
        > franchID franchName
        > LAD The Dodgers
        >
        > or whatever you want to put in there.
        >
        > Isn't this more logical than the current setup? And doesn't it improve
        > functionality? For example, you could now do:
        >
        > SELECT teamName, sum(g) as G FROM teams t LEFT JOIN teams_lkup d USING
        > (teamID) WHERE t.franchID=' LAD' GROUP BY t.teamid;
        >
        > and get:
        >
        > teamName G
        > BR3* 783
        > Brooklyn Dodgers 10752
        > Los Angeles Dodgers 7788
        >
        > * See, I don't even know who BR3 is? You should be able to recover the
        > name of that team!
        >
        > -Ben
        >
        >
        > http://www.baseball -databank. org/
        > Yahoo! Groups Links
        >
        >
        >
        >


        Need Mail bonding?
        Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.

      • Derek Adair
        Yes - I separate those out in their lines in Teams.txt as well. Regards, Derek
        Message 3 of 6 , Jun 26, 2007
        • 0 Attachment
          Yes - I separate those out in their lines in Teams.txt as well.

          Regards,
          Derek


          On Tue, 26 Jun 2007, robert bluestein wrote:

          > but they were also the Bridegrooms and the Robins at one time. Should we break that up too?
          >
          > Derek Adair <dadair@...> wrote: On a related note, since you brought up table structure:
          >
          > I've always felt that "Brooklyn" and "Dodgers" (location and team
          > nickname) should be separate fields in the database, and I separate them
          > as such when I import.
          >
          > That helps out quite a big when doing some of the queries mentioned (show
          > me all the Washington teams, for example).
          >
          > Regards,
          > Derek
          >
          > On Tue, 26 Jun 2007, Ben Baumer wrote:
          >
          >> I think Tom is right. Why not add another layer of information if it
          >> adds something and doesn't take anything away? It wouldn't be that hard
          >> to populate.
          >>
          >> On a slightly different note, I think that there are other ways to
          >> improve the structure of the implementation of "teams". For example,
          >> there are 149 distinct teamIDs, but only 120 distinct franchIDs. As far
          >> as I can tell, there is no lookup table for each teamID, so for example,
          >> there is no way to generate a list of teams that includes the string
          >> "Brooklyn Dodgers". Why should you be able to generate a similar list
          >> with the phrase "Los Angeles Dodgers"?
          >>
          >> In my view, and I think in terms of 3NF design, instead of the two
          >> tables "teams" and "teamsfranchises", there should be three tables:
          >> "teams", "teams_lkup", and "franch_lkup". The first would be the same as
          >> it is now. The second would have one and only one entry for each
          >> distinct teamID that appears in "teams", and have entries like:
          >>
          >> teamID teamName firstYear lastYear lgID active
          >> BRO Brooklyn Dodgers 1890 1957 NL N
          >> LAD Los Angeles Dodgers 1958 NULL NL Y
          >> .
          >> .
          >> .
          >>
          >> Then, the "franch_lkup" table would have a unique key for each franchID
          >> that appears in "teams", so it would look like this:
          >>
          >> franchID franchName
          >> LAD The Dodgers
          >>
          >> or whatever you want to put in there.
          >>
          >> Isn't this more logical than the current setup? And doesn't it improve
          >> functionality? For example, you could now do:
          >>
          >> SELECT teamName, sum(g) as G FROM teams t LEFT JOIN teams_lkup d USING
          >> (teamID) WHERE t.franchID='LAD' GROUP BY t.teamid;
          >>
          >> and get:
          >>
          >> teamName G
          >> BR3* 783
          >> Brooklyn Dodgers 10752
          >> Los Angeles Dodgers 7788
          >>
          >> * See, I don't even know who BR3 is? You should be able to recover the
          >> name of that team!
          >>
          >> -Ben
          >>
          >>
          >> http://www.baseball-databank.org/
          >> Yahoo! Groups Links
          >>
          >>
          >>
          >>
          >
          >
          >
          >
          >
          > ---------------------------------
          > Need Mail bonding?
          > Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
        • Zach
          ... or Colts, Orphans, Cubs, White Stockings, Redlegs, California, Los Angeles, Anaheim, Los Angeles of Anaheim, etc. If you are doing a look up for a specific
          Message 4 of 6 , Jun 26, 2007
          • 0 Attachment
            ... or Colts, Orphans, Cubs, White Stockings, Redlegs, California, Los Angeles, Anaheim, Los
            Angeles of Anaheim, etc.

            If you are doing a look up for a specific city... does LA include the Angels? Or just the Angels
            during the periods they were from LA - regardless of if they moved or not?

            I do not think there is anything wrong with the idea - as an add on type thing. But there is so
            much gray area that it would be very confusing to "officially" add it.

            Zach


            --- robert bluestein <robertbluesteinphotography@...> wrote:

            > but they were also the Bridegrooms and the Robins at one time. Should we break that up too?
            >
            > Derek Adair <dadair@...> wrote: On a related note, since you brought up table
            > structure:
            >
            > I've always felt that "Brooklyn" and "Dodgers" (location and team
            > nickname) should be separate fields in the database, and I separate them
            > as such when I import.
            >
            > That helps out quite a big when doing some of the queries mentioned (show
            > me all the Washington teams, for example).
            >
            > Regards,
            > Derek
            >
            > On Tue, 26 Jun 2007, Ben Baumer wrote:
            >
            > > I think Tom is right. Why not add another layer of information if it
            > > adds something and doesn't take anything away? It wouldn't be that hard
            > > to populate.
            > >
            > > On a slightly different note, I think that there are other ways to
            > > improve the structure of the implementation of "teams". For example,
            > > there are 149 distinct teamIDs, but only 120 distinct franchIDs. As far
            > > as I can tell, there is no lookup table for each teamID, so for example,
            > > there is no way to generate a list of teams that includes the string
            > > "Brooklyn Dodgers". Why should you be able to generate a similar list
            > > with the phrase "Los Angeles Dodgers"?
            > >
            > > In my view, and I think in terms of 3NF design, instead of the two
            > > tables "teams" and "teamsfranchises", there should be three tables:
            > > "teams", "teams_lkup", and "franch_lkup". The first would be the same as
            > > it is now. The second would have one and only one entry for each
            > > distinct teamID that appears in "teams", and have entries like:
            > >
            > > teamID teamName firstYear lastYear lgID active
            > > BRO Brooklyn Dodgers 1890 1957 NL N
            > > LAD Los Angeles Dodgers 1958 NULL NL Y
            > > .
            > > .
            > > .
            > >
            > > Then, the "franch_lkup" table would have a unique key for each franchID
            > > that appears in "teams", so it would look like this:
            > >
            > > franchID franchName
            > > LAD The Dodgers
            > >
            > > or whatever you want to put in there.
            > >
            > > Isn't this more logical than the current setup? And doesn't it improve
            > > functionality? For example, you could now do:
            > >
            > > SELECT teamName, sum(g) as G FROM teams t LEFT JOIN teams_lkup d USING
            > > (teamID) WHERE t.franchID='LAD' GROUP BY t.teamid;
            > >
            > > and get:
            > >
            > > teamName G
            > > BR3* 783
            > > Brooklyn Dodgers 10752
            > > Los Angeles Dodgers 7788
            > >
            > > * See, I don't even know who BR3 is? You should be able to recover the
            > > name of that team!
            > >
            > > -Ben
            > >
            > >
            > > http://www.baseball-databank.org/
            > > Yahoo! Groups Links
            > >
            > >
            > >
            > >
            >
            >
            >
            >
            >
            > ---------------------------------
            > Need Mail bonding?
            > Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.




            ____________________________________________________________________________________
            Sick sense of humor? Visit Yahoo! TV's
            Comedy with an Edge to see what's on, when.
            http://tv.yahoo.com/collections/222
          • Mike Crain
            I agree with the Seans. I do n know if it is needed. I think the database s job is to report on historical data, impartial of what people thought. If this is
            Message 5 of 6 , Jun 26, 2007
            • 0 Attachment
              I agree with the Seans. I do'n know if it is needed.
               
              I think the database's job is to report on historical data, impartial of what people thought. If this is something that SOME of us should want, then put it in the FILES section as extra table, like an add-in. My personal copy has more table than what is in the BDB , and may always do so.  While I think some may be a good addition (umpires, coaches), they report on historical fact. While some Washingtonians may believe that they are a continuation of the earlier teams, people like myself will not.
               
               
              Incidently, In some cases I combine multiple BDB tables into one, but that's a side note.

              ----- Original Message ----
              From: Zach <fantasysportswire@...>
              To: baseball-databank@yahoogroups.com
              Sent: Tuesday, June 26, 2007 4:07:52 PM
              Subject: RE: [baseball-databank] Digest Number A922

              ... or Colts, Orphans, Cubs, White Stockings, Redlegs, California, Los Angeles, Anaheim, Los
              Angeles of Anaheim, etc.

              If you are doing a look up for a specific city... does LA include the Angels? Or just the Angels
              during the periods they were from LA - regardless of if they moved or not?

              I do not think there is anything wrong with the idea - as an add on type thing. But there is so
              much gray area that it would be very confusing to "officially" add it.

              Zach

              --- robert bluestein <robertbluesteinphot ography@yahoo. com> wrote:

              > but they were also the Bridegrooms and the Robins at one time. Should we break that up too?
              >
              > Derek Adair <dadair@iglou. com> wrote: On a related note, since you brought up table
              > structure:
              >
              > I've
              always felt that "Brooklyn" and "Dodgers" (location and team
              > nickname) should be separate fields in the database, and I separate them
              > as such when I import.
              >
              > That helps out quite a big when doing some of the queries mentioned (show
              > me all the Washington teams, for example).
              >
              > Regards,
              > Derek
              >
              > On Tue, 26 Jun 2007, Ben Baumer wrote:
              >
              > > I think Tom is right. Why not add another layer of information if it
              > > adds something and doesn't take anything away? It wouldn't be that hard
              > > to populate.
              > >
              > > On a slightly different note, I think that there are other ways to
              > > improve the structure of the implementation of "teams". For example,
              > > there are 149 distinct teamIDs, but only 120 distinct franchIDs. As far
              > > as I can tell, there is no lookup table for each teamID, so for example,
              > > there is no
              way to generate a list of teams that includes the string
              > > "Brooklyn Dodgers". Why should you be able to generate a similar list
              > > with the phrase "Los Angeles Dodgers"?
              > >
              > > In my view, and I think in terms of 3NF design, instead of the two
              > > tables "teams" and "teamsfranchises" , there should be three tables:
              > > "teams", "teams_lkup" , and "franch_lkup" . The first would be the same as
              > > it is now. The second would have one and only one entry for each
              > > distinct teamID that appears in "teams", and have entries like:
              > >
              > > teamID teamName firstYear lastYear lgID active
              > > BRO Brooklyn Dodgers 1890 1957 NL N
              > > LAD Los Angeles Dodgers 1958 NULL NL Y
              > > .
              > > .
              > > .
              > >
              > > Then, the "franch_lkup" table would have a unique key for each franchID
              > > that appears in "teams", so it would look like
              this:
              > >
              > > franchID franchName
              > > LAD The Dodgers
              > >
              > > or whatever you want to put in there.
              > >
              > > Isn't this more logical than the current setup? And doesn't it improve
              > > functionality? For example, you could now do:
              > >
              > > SELECT teamName, sum(g) as G FROM teams t LEFT JOIN teams_lkup d USING
              > > (teamID) WHERE t.franchID=' LAD' GROUP BY t.teamid;
              > >
              > > and get:
              > >
              > > teamName G
              > > BR3* 783
              > > Brooklyn Dodgers 10752
              > > Los Angeles Dodgers 7788
              > >
              > > * See, I don't even know who BR3 is? You should be able to recover the
              > > name of that team!
              > >
              > > -Ben
              > >
              > >
              > > http://www.baseball -databank. org/
              > > Yahoo! Groups Links
              > >
              > >
              > >
              > >
              >
              >
              >
              >
              >
              > ------------ --------- --------- ---
              > Need Mail bonding?
              > Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.

              ____________ _________ _________ _________ _________ _________ _
              Sick sense of humor? Visit Yahoo! TV's
              Comedy with an Edge to see what's on, when.
              http://tv.yahoo. com/collections/ 222


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