How to lay out my database?

Question

I'm creating an online store to sell firearms and related items. I've been working on getting the database (MySQL) perfect since it'll be the backbone of the site. My problem is I can't wrap my head around how the database should be laid out while keeping normalization a priority. The site has many products and most have the same information, but many have subtle differences.

For example, with ammunition there would be four categories and three of the four have the same information and the last has differences. Would I make one table for the three that are the same and a separate table for the one that has different information? Or would I create the table and have a column that can house an array or JSON string with the differences for that product?

Keep in mind there are many products that have the same issues. I'm not afraid of work, I just want to make sure I'm headed on the right path.

Let me give you a brief layout of my categories:

• Firearms
    • handguns (unique characteristics)
    • centerfire rifles (unique characteristics)
    • rimfire rifles (same as above)
• Ammunition
    • rifle ammo (unique characteristics)
    • handgun ammo (same as above)
    • rimfire ammo (same as above)
    • shotgun ammo (unique characteristics)
• Shooting Accessories
    • there are many products here with unique characteristics
• Optics
    • there are many products here with unique characteristics
• MANY MORE ...

I would love to make one table called products and put them all in that table, but with all the subtle difference I'm sure that cannot be possible.

*EDIT*

Here is an example of the attribute differences I will have:

• Rifle Ammunition
  • manufacturer
  • model
  • caliber
  • bullet weight
  • bullet type
  • number of rounds
  • price
  • description
  • sku
  • rating

• Handgun Ammunition
  • SAME AS ABOVE

• Rimfire Ammunition
  • SAME AS ABOVE

• Shotgun Ammunition
  • manufacturer
  • model
  • gauge
  • shell length
  • shot weight
  • shot size
  • velocity
  • price
  • description
  • sku
  • rating

The rifle, handgun, and rimfire will have the same amount of attributes (10). Shotgun will have (11) attributes and there are many more instances like this problem.

Thanks for having a look :)

Answer 1

I would do something like this:

The product table would be pretty basic, your main product details:

create table products
(
  id int,
  name varchar(50)
);

insert into products values
(1, 'rifle ammunition'),
(2, 'handgun ammo');

I personally would use a model similar to the following:

The product table would be pretty basic, your main product details:

create table product
(
  part_number int, (PK)
  name varchar(10),
  price int
);
insert into product values
(1, 'product1', 50),
(2, 'product2', 95.99);

Second the attribute table to store the each of the different attributes.

create table attributes
(
  id int,
  attribute_name varchar(10),
  attribute_value varchar(10)
);

insert into attributes values
(1, 'manufacturer', 'test'),
(2, 'model', 'blah'),
(3, 'bullet weight', '10'),
(4, 'bullet type', 'metal'),
(5, 'price', '50');

Finally create the product_attribute table as the JOIN table between each product and its attributes associated with it. This table will allow for multiple entries for each product and the attributes. So if one product has 10 attributes, there will be 10 entries. If another has 2 attributes, there will be two entries.

create table products_attributes 
(
  product_id int,
  attribute_id int
);

insert into products_attributes values
(1,  1),
(1,  2),
(1,  3),
(1,  4),
(2,  1),
(2,  2),
(2,  3);

This allows you to be flexible with having many products with multiple attribues or categories. Then querying would be simple with a few joins (See SQL Fiddle With Demo):

select *
from products p
left join products_attributes pa
  on p.id = pa.product_id
left join attributes a
  on pa.attribute_id = a.id;

The results of the query would be:

ID | NAME              | PRODUCT_ID | ATTRIBUTE_ID | ATTRIBUTE_NAME | ATTRIBUTE_VALUE
-------------------------------------------------------------------------------
1  | rifle ammunition  | 1          | 1            | manufacturer   | test
1  | rifle ammunition  | 1          | 2            | model          | blah
1  | rifle ammunition  | 1          | 3            | bullet weight  | 10
1  | rifle ammunition  | 1          | 4            | bullet type    | metal
2  | handgun ammo      | 2          | 1            | manufacturer   | test
2  | handgun ammo      | 2          | 2            | model          | blah
2  | handgun ammo      | 2          | 3            | bullet weight  | 10

Answer 2

Here's a simple design:

Product

• product_id
• product_name

Product_Category

• product_id
• category_id

Category

• category_id
• category_name

With this design, each product can have multiple categories. If you add a category_parent to the Category table, you can have sub-categories.

Update 1

Using 'attributes' in place of 'categories' and adding value to the attribute:

Product

• product_id
• product_name

Product_Attribute

• product_id
• attribute_id
• attribute_value

attribute

• attribute_id
• attribute_name

Answer 3

There are many ways you could go about doing this. Perhaps the other answers are the best, but the first thing that popped into my head would be to do something like this:

CREATE TABLE ammunition (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    -- any columns that are common across ALL categories of ammo,
    PRIMARY KEY (id)
);

CREATE TABLE rifleHandgunRimfireAmmo (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    -- any columns that are exclusive to rifle/handgun/rimfire ammo,
    fkAmmoID INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (fkAmmoID) REFERENCES ammunition (id)
);

CREATE TABLE shotgunAmmo (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    -- any columns that are exclusive to shotgun ammo,
    fkAmmoID INTEGER UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (fkAmmoID) REFERENCES ammunition (id)
);

It's basically like OOP. You have a base table (kinda like a base class), ammunition that holds all common information. You then extend that base table with child tables (like child classes). Those child tables hold information more specific to that particular object type.

Like I said, not sure if this is the best way to do it, or if the solutions by the other posters would work better, but it's just what popped into my head.