CODEWITHSUNDEEP
mysqldatabasedatabase-design
NotSoSmart
NotSoSmart

Reputation: 89

Is there a name for this database type?

I've tried searching for this, but I don't really know what i'm searching for.

Basically, i want to store match data in a database, but when I get a new team, i need to add the team name to the row and the column. I also need to store more than just the score, I can use an array to seperate the data, but it'd be handy to have at least two fields at each point so that I could search home and away leg data.

    Dan | Bob | Joe | Tim
Dan  X  | 3-1 | 0-1 | 2-1
Bob 4-1 |  X  | 1-0 | 0-0
Joe 1-2 | 3-3 |  X  | 2-1
Tim 0-1 | 0-0 | 2-1 |  X

Is there a way to do this with MySQL so that I could add an unlimited amount of teams over time? Is there a name for this type of database format?

Upvotes: 0

Views: 53

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

The name is "many-to-many relationship".

It is modeled by having a separate (so called "junction" or "link") table that references both endpoints of the relationship (which in this case happens to be the same table).

For example:

CREATE TABLE TEAM (
    TEAM_ID INT PRIMARY KEY
    -- Other fields...
);

CREATE TABLE SCORE (
    TEAM1_ID INT,
    TEAM2_ID INT,
    -- Other field(s) that descibe the score...
    PRIMARY KEY (TEAM1_ID, TEAM2_ID),
    FOREIGN KEY (TEAM1_ID) REFERENCES TEAM (TEAM_ID),
    FOREIGN KEY (TEAM2_ID) REFERENCES TEAM (TEAM_ID)
);

Upvotes: 2

Ulrich Horus
Ulrich Horus

Reputation: 350

in a simpler way: your table must look like:

Player1  | Player 2 | scorePlayer1 | scorePlayer2
  Dan        Joe             0             1

and so on but you can still optimized the table

Upvotes: 0

Barranka
Barranka

Reputation: 21047

You need two tables:

  • teams
  • match data

Let's say your tables are something like this:

create table teams (
    teamId int unsigned not null primary key,
    teamName varchar(50)
);

create table matchData (
    team1id int unsigned not null,
    team2id int unsigned not null,
    matchData varchar(50)  -- just an example
);

Insert teams in the appropriate table (don't insert duplicates), and then insert the match data (of course, you need to ensure that team1id != team2id for every record of matchData).

As for the way you want to display the data, it is up to you to design the front-end interface... MySQL does not provide built-in tools to create "pivot tables", but it is easy to display a matrix using another language (Java, PHP, etcetera).

Upvotes: 2

Related Questions