Feedback on database design

[Jay Ellsworth]

Hello,

I'm a newbie developer and I'm just wondering if these forums would be an appropriate place to post the designs for a database I'm working on? I'm working on a site that people can use to keep track of their game collection. It's the first database I've designed entirely by myself and I'm looking for a place to post it so I can get some feedback on it. I see there's a thread for posting finished sites, is there a similar thread for databases?

Thanks,

Jay

[justsomeguy]

You can post that stuff here.

[Jay Ellsworth]

Perfect!

Ok, so for this first post 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 I attached my EER diagram, but 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

[justsomeguy]

Are the GameTagID and GamePlatformID columns in the game table left over from something else?  It doesn't look like those are necessary.  For the gameplatform and gametag tables, I would get rid of the int primary key and make both of the other columns part of the primary key, so that you can enforce unique data there.

[Jay Ellsworth]

No they're not left overs, the GameTag and GamePlatform tables are the intermediary tables to satisfy the many-to-many relationships between Game and Tag and Game and Platform respectively. They both contain their own ID and both foreign keys of the tables they connect. They just don't look necessary because my relationships aren't visible. I could do a composite key like you're saying, but as-is I have a dedicated ID for each table for uniqueness.

[justsomeguy]

I'm talking about the columns in the game table, not the gametag and gameplatform tables.

I have a dedicated ID for each table for uniqueness.

The unique constraint on something like the gametag table should be the gameID and tagID, not an autonumber.  The combination of the gameID and tagID is what needs to be unique, you wouldn't want multiple records with the same values for those fields.  That's why it should be the primary key, and making it the primary key will also help with searching.

[Jay Ellsworth]

Oh ok, you're right and I see what you're saying now. Game to GameTag for example is one-to-many so GameTag has GameID as a foreign key, that makes sense. I had it in my head that the columns had to exist in both tables for some reason. 

I understand composite keys like you're saying, but I didn't realize that would help with searching, I just want it to be designed correctly and run well. I will take your advice and get each of those changed to use composite keys instead.

Thanks for the reply, I appreciate the help