How To Setup Mysql Table Structure For Gameshop

[Richard]

<p>Hello,

 

I have difficulty figuring out how to setup mysql table structure for gameshop like www.gamestop.com.

Each category/platform should have own store. User should also be able to browse by subcategory.

 

Am i heading in the right direction, or should i take different approach.

 

Here are the tables so far:

 

--

-- Database: `gameshop`

--

 

-- --------------------------------------------------------

 

--

-- Table structure for table `games`

--

 

CREATE TABLE `games` (

  `id` SMALLINT NOT NULL AUTO_INCREMENT,

  `platform` VARCHAR(30) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `platform` (`platform`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

-- --------------------------------------------------------

 

--

-- Dumping data for table `games`

--

 

INSERT INTO `games` (`id`, `platform`) VALUES

(1, 'Xbox360'),

(2, 'PS3'),

(3, 'PC'),

(4, '3DS'),

(5, 'VITA'),

(6, 'WiiU');

 

-- --------------------------------------------------------

 

 

--

-- Table structure for table `game_products`

--

 

CREATE TABLE `game_products` (

  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

  `platform_id` tinyint(3) unsigned NOT NULL,

  `name` varchar(60) NOT NULL,

  `description` tinytext,

  `publisher` varchar(80) NOT NULL,

  `developer` varchar(80) NOT NULL,

  `genre` varchar(20) NOT NULL,

  `image` varchar(45) NOT NULL,

  `price` decimal(5,2) unsigned NOT NULL,

  `stock` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

 

--

-- Dumping data for table `game_products`

--

 

INSERT INTO `products` VALUES(1, 1, 'Gears of War 3', 'Description, Description, Description ','Microsoft','Epic Games','FPS','image1.jpg', 36.50, 10, '2012-06-15 12:22:35');

 

-- --------------------------------------------------------

 

 

 

CREATE TABLE `accessories` (

  `id` SMALLINT NOT NULL AUTO_INCREMENT,

  `acc_category` VARCHAR(30) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `platform` (`platform`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

-- --------------------------------------------------------

 

--

-- Table structure for table `accessories_products`

--

 

CREATE TABLE `accessories_products` (

  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

  `acc_category_id` tinyint(3) unsigned NOT NULL,

  `name` varchar(60) NOT NULL,

  `description` tinytext,

  `image` varchar(45) NOT NULL,

  `price` decimal(5,2) unsigned NOT NULL,

  `stock` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),

  UNIQUE KEY `name` (`name`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

 

 

-- --------------------------------------------------------

                                        

CREATE TABLE `used` (

  `id` SMALLINT NOT NULL AUTO_INCREMENT,

  `platform` VARCHAR(30) NOT NULL,                    ------> game_products

  PRIMARY KEY (`id`),

  UNIQUE KEY `platform` (`platform`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

-- --------------------------------------------------------p>Thank you in advance.

[Edward]

More tables need to be made, you will have repeated values in columns for publishers like Microsoft and other columns. Php & MySQL for dynamic websites 4th edition covers the rules of normalization, you really need to read that over.

[Edward]

You would probably be fine justing scrapping the used table and adding in a column in game_products with a Boolean value. Because this is done for Male and Female although it can break the normalization rules it can be accepted

[Richard]

More tables need to be made, you will have repeated values in columns for publishers like Microsoft and other columns. Php & MySQL for dynamic websites 4th edition covers the rules of normalization, you really need to read that over.

 

More tables will be made. This is just to see am i heading in the right direction.Considering the book issue i don't have yet, but will buy for sure.For your second post: Changed. Thank you for your time.

[Edward]

Have you seen gog.com? They have must have a database full of this kind of criteria.

[Antonio Conte]

This seem pretty good already. You are definitely going in the right direction.

 

Edward is correct about a new publisher table. The same can be said for "genre" in your games table. Either way. Normalization is mostly to ensure consistency. You may consider not spilling, let's say developers, into a new table even though that would be "correct". It won't really break data integrity. From that point of view, this looks very good. Improve as you see fit.

 

Also, consider changing from MyISAM to InnoDB. The latter allows foreign keys, something that will greatly benefit you here.

 

I don't really get your "used" table. What's it for?

[Richard]

Have you seen gog.com? They have must have a database full of this kind of criteria.

 

 

I haven't seen them before. Website looks very interesting.

[Richard]

This seem pretty good already. You are definitely going in the right direction.

 

Edward is correct about a new publisher table. The same can be said for "genre" in your games table. Either way. Normalization is mostly to ensure consistency. You may consider not spilling, let's say developers, into a new table even though that would be "correct". It won't really break data integrity. From that point of view, this looks very good. Improve as you see fit.

 

Also, consider changing from MyISAM to InnoDB. The latter allows foreign keys, something that will greatly benefit you here.

 

I don't really get your "used" table. What's it for?

 

 

Some relief. I will modify my tables as you suggested.

 

"used" is for "used games". The game will be either new or used. As Edward mention in #3 i scrap the the "used" table and add new column in game products table.

 

I will also made a new tables for "publisher" and "genre".

 

Thank you for your time.