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

REPORT - DB Design Committee - initial draft

Expand Messages
  • tangotiger <tmasc@yahoo.com>
    Tom Lewis and I have completed our initial normalization of the database. (Expect the 200KB zip file to be uploaded in 2 minutes.) Here is what you will see:
    Message 1 of 8 , Jan 31, 2003
    • 0 Attachment
      Tom Lewis and I have completed our initial normalization of the
      database. (Expect the 200KB zip file to be uploaded in 2 minutes.)

      Here is what you will see:
      1 - a log of all changes, step-by-step, so that you can follow. If
      you decide to apply them to your database, I would make a copy
      first. This is still an initial draft.

      2 - A picture layout of the E-R with *all* 34 tables.

      3 - The Access 2000 database, with complete structure (but no data).

      Here is what we are looking for:
      a - comments on any tables that we need, or normalization that we
      missed

      b - comments on any fields that we need or should be moved somewhere
      else

      c - any other comments

      Here is what you should keep in mind:
      i - the intended audience is a DB administrator. It is conceivable,
      even probable, that a more-user-friendly version of the database can
      be spunoff that would maybe have less tables, and would certainly
      have more redundancy. Please keep this in mind when reviewing the DB.

      ii - Currently, the biggest job is to integrate KJOK's park database
      into the BDB, so that we can safely remove the "park" field from the
      BDB.

      iii - The home/road splits. It looks like the BDB has the Palmer/TB
      PF. This really has no place here, but we shouldn't lose it until we
      integrate the H/R splits. Again, KJOK's database, plus some of
      Derek's files, also has all this.

      iiii - The one place where we deviated from normal design practices
      is to *not* have a random/sequential key for each table (i.e., we
      kept everything as-is). The reason is that because of the
      collaborative effort, and the need to not have to download the whole
      DB, the keys had to mean something (contrary to good design
      practices). This was an important exception to make on the design
      front, but works out well for everyone involved. Essentially, don't
      worry about what I just said, because nothing happened. We're just
      telling you the reason.

      Thanks, T&T
    • Sean Forman
      ... Good job guys. I m sorry, but I have to ask this. Do you have a reference that suggests doing this? I m just not familiar with designing all the tables
      Message 2 of 8 , Jan 31, 2003
      • 0 Attachment
        > iiii - The one place where we deviated from normal design practices
        > is to *not* have a random/sequential key for each table (i.e., we
        > kept everything as-is). The reason is that because of the
        > collaborative effort, and the need to not have to download the whole
        > DB, the keys had to mean something (contrary to good design
        > practices). This was an important exception to make on the design
        > front, but works out well for everyone involved. Essentially, don't
        > worry about what I just said, because nothing happened. We're just
        > telling you the reason.
        >
        > Thanks, T&T


        Good job guys.

        I'm sorry, but I have to ask this. Do you have a reference that
        suggests doing this? I'm just not familiar with designing all the
        tables in a DB this way.

        --
        Sincerely,
        Sean Forman

        Baseball Stats! http://www.Baseball-Reference.com/
        Baseball Analysis! http://www.BaseballPrimer.com/
      • Kevin <kjokbaseball@yahoo.com>
        I m probably missing something here, but isn t all we need is a Park/Team Xref Table? Something like: ParkID, TeamID, Year STL07, SLN, 1931 STL07, SLA, 1931
        Message 3 of 8 , Jan 31, 2003
        • 0 Attachment
          I'm probably missing something here, but isn't all we need is a
          Park/Team Xref Table? Something like:

          ParkID, TeamID, Year
          STL07, SLN, 1931
          STL07, SLA, 1931

          etc.????

          THANKS,
          KJOK

          --- In baseball-databank@yahoogroups.com, "tangotiger <tmasc@y...>"
          <tmasc@y...> wrote:
          ............> ii - Currently, the biggest job is to integrate KJOK's
          park database
          > into the BDB, so that we can safely remove the "park" field from
          the
          > BDB.
          .......................>
          > Thanks, T&T
        • tmasc@yahoo.com
          The BDB has park NAME and not Park ID. So, we have to resolve that first, then we can do as you say. Tom ...
          Message 4 of 8 , Jan 31, 2003
          • 0 Attachment
            The BDB has park NAME and not Park ID. So, we have to
            resolve that first, then we can do as you say.

            Tom
            --- "Kevin <kjokbaseball@...>"
            <kjokbaseball@...> wrote:
            > I'm probably missing something here, but isn't all
            > we need is a
            > Park/Team Xref Table? Something like:
            >
            > ParkID, TeamID, Year
            > STL07, SLN, 1931
            > STL07, SLA, 1931
            >
            > etc.????
            >
            > THANKS,
            > KJOK
            >
            > --- In baseball-databank@yahoogroups.com,
            > "tangotiger <tmasc@y...>"
            > <tmasc@y...> wrote:
            > ............> ii - Currently, the biggest job is to
            > integrate KJOK's
            > park database
            > > into the BDB, so that we can safely remove the
            > "park" field from
            > the
            > > BDB.
            > .......................>
            > > Thanks, T&T
            >
            >


            __________________________________________________
            Do you Yahoo!?
            Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
            http://mailplus.yahoo.com
          • tmasc@yahoo.com
            As well, KJOK DB has a different Team ID than the BDB. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful.
            Message 5 of 8 , Jan 31, 2003
            • 0 Attachment
              As well, KJOK DB has a different Team ID than the BDB.

              __________________________________________________
              Do you Yahoo!?
              Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
              http://mailplus.yahoo.com
            • Kevin <kjokbaseball@yahoo.com>
              Converting Park NAME to Park ID is relatively painless. I believe the only unresolved issue was what to do about teams that played in more than one park in
              Message 6 of 8 , Jan 31, 2003
              • 0 Attachment
                Converting Park NAME to Park ID is relatively painless. I believe
                the only unresolved issue was what to do about teams that played in
                more than one park in one season, but since there is currently only
                one park NAME per team/season in the database, that issue doesn't
                have to be immediately resolved.

                THANKS,
                KJOK

                --- In baseball-databank@yahoogroups.com, "tmasc@y..." <tmasc@y...>
                wrote:
                > The BDB has park NAME and not Park ID. So, we have to
                > resolve that first, then we can do as you say.
                >
                > Tom
                > --- "Kevin <kjokbaseball@y...>"
                > <kjokbaseball@y...> wrote:
                > > I'm probably missing something here, but isn't all
                > > we need is a
                > > Park/Team Xref Table? Something like:
                > >
                > > ParkID, TeamID, Year
                > > STL07, SLN, 1931
                > > STL07, SLA, 1931
                > >
                > > etc.????
                > >
                > > THANKS,
                > > KJOK
                > >
                > > --- In baseball-databank@yahoogroups.com,
                > > "tangotiger <tmasc@y...>"
                > > <tmasc@y...> wrote:
                > > ............> ii - Currently, the biggest job is to
                > > integrate KJOK's
                > > park database
                > > > into the BDB, so that we can safely remove the
                > > "park" field from
                > > the
                > > > BDB.
                > > .......................>
                > > > Thanks, T&T
                > >
                > >
                >
                >
                > __________________________________________________
                > Do you Yahoo!?
                > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
                > http://mailplus.yahoo.com
              • Kevin <kjokbaseball@yahoo.com>
                I will happily replace the Team ID in my database with Lahman Team ID or Retrosheet Team ID or even create columns for both (except that would create
                Message 7 of 8 , Jan 31, 2003
                • 0 Attachment
                  I will happily replace the Team ID in my database with Lahman Team ID
                  or Retrosheet Team ID or even create columns for both (except that
                  would create redundancy....)

                  THANKS,
                  KJOK

                  --- In baseball-databank@yahoogroups.com, "tmasc@y..." <tmasc@y...>
                  wrote:
                  > As well, KJOK DB has a different Team ID than the BDB.
                  >
                  > __________________________________________________
                  > Do you Yahoo!?
                  > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
                  > http://mailplus.yahoo.com
                • tangotiger <tmasc@yahoo.com>
                  ... Here s a good primer on key construction. And I should have said, as this article demonstrates, that it applies to all parent tables, though you can still
                  Message 8 of 8 , Feb 3, 2003
                  • 0 Attachment
                    --- In baseball-databank@yahoogroups.com, Sean Forman <sean-
                    forman@b...> wrote:
                    > I'm sorry, but I have to ask this. Do you have a reference that
                    > suggests doing this? I'm just not familiar with designing all the
                    > tables in a DB this way.
                    >

                    Here's a good primer on key construction. And I should have said, as
                    this article demonstrates, that it applies to all parent tables,
                    though you can still argue for all tables in some instances.

                    http://www.cecs.csulb.edu/~jewett/dbdesign/dbdesign.php?
                    page=keys.php&imgsize=medium

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