What database technique should I use?

Question

I am new to databases. This is for a class. I am using MySQL. I will be accessing the database with PHP. I have two tables already. TableA is for products. TableB is for US States. I want to have data about sales of each product in each state. I have considered this is a many to many relationship.

Technique idea #1:

I have considered making a third table, TableC, that has a column for the state names and a column for each product. My issue with this is that I don't know how create a relationship between the product rows in TableA and the product columns in TableC. If I add a product to TableA I want it to automatically add the column in TableC.

Technique idea #2:

Add the product columns to TableB. Same issue as above. Also, seems like a worse design.

Are one of these techniques the right way to do this or is there another technique?

I will give you a hint to start: a person ORDERS a product in a particular state, that is a sale transaction — Brian Ogden, Apr 29 '17 at 22:18
Related: [How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) — Jonathan Lonowski, Apr 29 '17 at 22:18
#1 - use third "link" table. It will include actual sales only. Use OUTER JOIN to get zeroes for products not available in a given state and such. — PM 77-1, Apr 29 '17 at 22:19

score 0 · Answer 1 · answered Apr 29 '17 at 23:29

The art and science of making a good schema revolves around finding the best place to put something, or in many cases, the least inconvenient.

Putting sales data in a table that's intended for geographic information is almost always a mistake. Keep your tables focused on one entity, and where information there exists only when related to other tables, make a "join table" that connects the two and put that data there.

For example:

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
);

CREATE TABLE states (
  id INT PRIMARY KEY AUTO_INCREMENT,
  code CHAR(2),
  name VARCHAR(255)
);

CREATE TABLE state_sales(
  id INT PRIMARY KEY AUTO_INCREMENT,
  state_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT
);

You can extend that later to include things like the date/month/quarter the sales happened in and so forth.

What database technique should I use?

1 Answers1