Jump to content

Jay Ellsworth

Members
  • Content count

    4
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Jay Ellsworth

  • Rank
    Newbie
  1. Feedback on database design

    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
  2. Feedback on database design

    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.
  3. Feedback on database design

    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
  4. Feedback on database design

    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
×