CODEWITHSUNDEEP
mysqldatabaseoracle-database
Julien Green
Julien Green

Reputation: 119

Relational Database (1 table per user for owned game)

I did not know how to make a title out of it but here s the question.

I have a table of users (lets say 8 millions people) and I wish to make a copy of Steam with all the Users owned game.

(figure 1) Would it be better to make a table per user with every owned game by the user?

OR

(figure 2) Would it be better to make the game table own Users who own the game.

enter image description here

Upvotes: 0

Views: 115

Answers (2)

Phiter
Phiter

Reputation: 14992

This is what you would need:

Table users

user_id  | user_name

1          Rick
2          Dwayne
3          Mark

CREATE TABLE `users` (
    `user_id` INT(11) NOT NULL,
    `user_name` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`user_id`)
)

Table games

game_id  | game_name
1          League of Legends
2          Dota
3          Skyrim

 CREATE TABLE `games` (
    `game_id` INT(11) NOT NULL,
    `game_name` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`game_id`)
)

Table owned_games

user_id  | game_id

1          1
1          3
3          2
2          1

CREATE TABLE `owned_games` (
    `user_id` INT(11) NOT NULL,
    `game_id` INT(11) NOT NULL,
    INDEX `FK_owned_games_users` (`user_id`),
    INDEX `FK_owned_games_games` (`game_id`),
    CONSTRAINT `FK_owned_games_games` FOREIGN KEY (`game_id`) REFERENCES `games` (`game_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `FK_owned_games_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE ON DELETE CASCADE
)

This is the perfect layout for your situation. The constraints will delete any rows in owned_games mentioning either a deleted user or game.

Upvotes: 2

OneCricketeer
OneCricketeer

Reputation: 191738

What is wrong with 3 tables?

Users: holds all user information

Games: holds all game information

OwnedGames: 2 columns. UserID and GameID. Holds a mapping of which Users own which Games.

Upvotes: 1

Related Questions