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.
