How to deal with 2 almost identical tables

Posted by jgritty on Stack Overflow See other posts from Stack Overflow or by jgritty
Published on 2012-12-20T04:19:43Z Indexed on 2012/12/20 5:03 UTC
Read the original article Hit count: 101

Filed under:
|

I have a table of baseball stats, something like this:

CREATE TABLE batting_stats(
    ab                INTEGER,
    pa                INTEGER,
    r                 INTEGER,
    h                 INTEGER,
    hr                INTEGER,
    rbi               INTEGER,
    playerID          INTEGER,
    FOREIGN KEY(playerID) REFERENCES player(playerID)
);

But then I have a table of stats that are basically exactly the same, but for a team:

CREATE TABLE team_batting_stats(
    ab                INTEGER,
    pa                INTEGER,
    r                 INTEGER,
    h                 INTEGER,
    hr                INTEGER,
    rbi               INTEGER,
    teamID            INTEGER,
    FOREIGN KEY(teamID) REFERENCES team(teamID)
);

My first instinct is to scrap the Foreign key and generalize the ID, but I still have a problem, I have these 2 tables, and they can't have overlapping IDs:

CREATE TABLE player(
    playerID    INTEGER PRIMARY KEY,
    firstname   TEXT,
    lastname    TEXT,
    number      INTEGER,
    teamID      INTEGER,
    FOREIGN KEY(teamID) REFERENCES team(teamID)
);

CREATE TABLE team(
    teamID      INTEGER PRIMARY KEY,
    name        TEXT,
    city        TEXT,
);

I feel like I'm overlooking something obvious that could solve this problem and reduce stats to a single table.

© Stack Overflow or respective owner

Related posts about sql

Related posts about sqlite