I'm working on a SQL database and trying to design My tables in a way that makes most sense.

The problem I am having is, imagine there are a bunch of video games. The games have some inherent properties, like genre, price, etc. now say you want to sell the game in GameStop and Toys r us. But each store models the properties of that game differently. So toys r us says the genre is action, but GameStop says its thriller. So obviously the columns would be differently named. Also, imagine that there can be different things that are sold down the line, like action figures , and there can be more stores added down the line as well.

Now my question is , which of these designs, if any, makes the most sense.

A)
Each item type (video games, action figures) are their own table. Each column in the table represents the inherent properties of that item (color, genre, weight, etc). Then you have separate tables for toys r us, GameStop, etc, with foreign keys to the table of video games, action figures, etc

each item type has their own table. In addition to the columns for their inherent properties, you now have additional columns for the stores. So for example, genre could be GameStop_genre for the GameStop genre column, and toys_r_us_genre for the toys r us genre column

C) each store type has their own table for each product. So you would have a gamestop_actionfigures table and a toysrus_actionfigures table, and you would create tables that way (store_itemtype). Additionally, you can have a separate table for action figures to show their raw inherent properties.

I feel like A is most logical, but C makes life easier for me. Instead of having to do joins and lookup additional info , you would have that information readily available in the table.

My apologies if this post is written poorly , I wrote it on my phone and was as frustrated typing as you probably were reading.

To start with, genre sounds like a many to one relationship. Don't stick it in a single column.

Depending on what kind of products you're selling, you might want custom templates. Instead of having a lot of columns to describe the game, Each tag would be a row in the Attributes table. Then you would have a Templates table that associates each type (Game) with a list of custom attributes. Then you have another table specifying the specific attribute for the specific product.

This allows the user to customize it without you having to add new tables every time something new comes up.

This post was edited by carteblanche on Feb 1 2016 11:47am