Sign in
Log inSign up

Feedback on database design

Jay Ellsworth's photo
Jay Ellsworth
·Aug 22, 2017

Hello,

Recent grad and newbie developer here hoping to find a community where I can post the first database I'm designing entirely by myself. I'm trying to make a site that people can use to keep track of their game collections. Currently using MySQL and trying to get the database design sorted out.

There are basically 5 tables:

  • Game
  • Tag
  • Platform
  • Developer
  • Publisher

Two of which have many-to-many relationships with the Game table, so each of those two have intermediary tables:

  • GameTag
  • GamePlatform

The Tag table is a combination of genre's and all the other descriptors that we use to describe games that aren't genre's, things like Open World, Co-op and Sandbox. Actual genre's will bubble to the top. And bear with me please, I realize some portions of development get farmed out to other developers and teams collaborate all the time, but for the sake of simplicity I've decided to make relationship between Developers and Games a one-to-many relationship.

Having said that, here are my scripts for creating the tables:

CREATE TABLE Developer
(
    DeveloperID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Developer VARCHAR(45)
);

CREATE TABLE Publisher
(
    PublisherID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Publisher VARCHAR(45)
);

CREATE TABLE Tag
(
    TagID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Tag VARCHAR(45),
    isGenre TINYINT DEFAULT 0
);

CREATE TABLE Platform
(
    PlatformID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Manufacturer VARCHAR(45),
    Platform VARCHAR(45)
);

CREATE TABLE Game
(
    GameID BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Game VARCHAR(45) NOT NULL,
    GameTagID INT UNSIGNED,
    GamePlatformID INT UNSIGNED,
    ReleaseDate DATE,
    DeveloperID INT UNSIGNED,
    PublisherID INT UNSIGNED,
    Comments mediumtext,
    Thumbnail BLOB,
    Backsplash BLOB,
    FOREIGN KEY (DeveloperID) REFERENCES Developer(DeveloperID),
    FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
);

CREATE TABLE GameTag
(
    GameTagID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    GameID BIGINT UNSIGNED,
    TagID INT UNSIGNED,
    FOREIGN KEY (GameID) REFERENCES Game(GameID),
    FOREIGN KEY (TagID) REFERENCES Tag(TagID)
);

CREATE TABLE GamePlatform
(
    GamePlatformID INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    GameID BIGINT UNSIGNED,
    PlatformID INT UNSIGNED,
    FOREIGN KEY (GameID) REFERENCES Game(GameID),
    FOREIGN KEY (PlatformID) REFERENCES Platform(PlatformID)
);

And here's my EER diagram:

imgur.com/a/Fk5FD

I'm actually having a problem getting the foreign key constraints to work as-is so none of the relationships are visible but here they are as they exist in my mind:

  • Game to Tag - M:N
  • Game to Platform - M:N
  • Developer to Game - 1:M
  • Publisher to Game - 1:M

And I have a DLC table that I haven't quite figured out what to do with yet. That can come later.

I'm using MySQL and again, hoping to get a little feedback on the layout of things. I appreciate any advice or suggestions anyone can offer. Any questions just ask.

Thanks,

Jay

9