0

I have a sales table that I want to record all the sales done by the employee. The problem i'm having is that I can only store one ProductId that comes from a Products table. What is wrong is that a sale has multiples products and with my current structure I can only store one ProductId. I know my approach is wrong but I just don't know how to properly fix it. The question I have is how do I store multiples products in the Sales Table.

This is my Sales Table columns.

CREATE TABLE `Sales` (
`SaleId` int(11) unsigned NOT NULL AUTO_INCREMENT,
`EmployeeId` int(11) unsigned NOT NULL,
`ProductId` int(11) unsigned NOT NULL,
PRIMARY KEY (`SaleId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Millenial2020
  • 2,465
  • 9
  • 38
  • 83

3 Answers3

1

You need a second table, a Sales-Product table:

CREATE TABLE `SaleProduct` (
`SaleId` int(11) unsigned NOT NULL,
`ProductId` int(11) unsigned NOT NULL,
`Quantity` int(11) unsigned NOT NULL,
PRIMARY KEY (`SaleId`, `ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and change your Sales Table:

CREATE TABLE `Sales` (
`SaleId` int(11) unsigned NOT NULL AUTO_INCREMENT,
`EmployeeId` int(11) unsigned NOT NULL,
PRIMARY KEY (`SaleId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SiggiSv
  • 1,219
  • 1
  • 10
  • 20
  • I would remove the `SaleProductID` column and change your primary key to `SaleId, ProductId `, and add a quantity column to the `SaleProduct` table – Jacob Lambert May 24 '15 at 17:14
  • @JRLambert You're correct. That would be a better answer. – SiggiSv May 24 '15 at 17:16
  • thanks for the answers. Using that approach how do one query a specify sale transaction. I'm having a hard time understand why use two primary keys in the SaleProduct table. Can you please a elaborate ? – Millenial2020 May 24 '15 at 18:21
  • @user3862830 For example if you want to query the sale with SaleId=102: `SELECT s.EmployeeId, sp.ProductId, sp.Quantity from Sales as s, SaleProduct as sp WHERE sp.SaleId=102 AND s.SaleId=sp.SaleId` will give you the ID of the employee and a list of the IDs of the sold products along with the quantity of each product. – SiggiSv May 25 '15 at 23:38
  • @user3862830 Regarding the SaleProduct table having two primary keys. Actually it's not two primary keys, it's one composite primary key made from two columns. Read the top answer here for more: http://stackoverflow.com/questions/217945/can-i-have-multiple-primary-keys-in-a-single-table – SiggiSv May 25 '15 at 23:49
1

Your table and approach is ok. You just have to insert one row in your sales table per product-id. The unique key for the sales is the SaleId, the foreign keys for Employee and Product are also there.

The only thing that's missing in your table is the sale quantity and amount.

It's the typical n:m-issue which is solved by the V-structure.

Kurt Ludikovsky
  • 682
  • 1
  • 6
  • 21
  • Your answer would benefit from an explanation of what the n:m-issue is, or an example of how the V-structure solves it! :) I understand what you mean, but I don't know those terms, so the answer raises other questions for me :) –  May 24 '15 at 17:21
  • I just tried to do this with a picture but the system doesn't allow me (to new to the system). So here is a link to the picture (hope that works) https://plus.google.com/109003368295236491052/posts/X3cEt31CkCN If you have many-to-many relation, in your case many salesperson sell many porducts, you need to resolve this with a link-table. In this case this is the Sales-table, which relates the sales-person to the Product, thereby denoting the amount and quantity (and possibly the date, region, etc.). In addition this might also be the link to the customer (via customer-ID) to a customer-table. – Kurt Ludikovsky May 24 '15 at 17:32
  • Good picture, thanks! (You can edit your answer and add it in when you get 10 rep.) What does n:m stand for? –  May 24 '15 at 18:22
  • M:N means that you have N sales persons and M Products. This is something you can't represent directly in a relation. In D-design you should normalize your data. See [link](http://en.wikipedia.org/wiki/Database_normalization). I also found a video here [link](https://www.youtube.com/watch?v=fg7r3DgS3rA). – Kurt Ludikovsky May 24 '15 at 21:26
  • The only problem i see with that approach is that if you want to query all the products for a particular Sale that would be impossible because every single Sale as a Primary Key number. – Millenial2020 May 25 '15 at 04:12
  • Take the real world: if you make a sale you have a delivery slip with 1. the slip number (uniqe per slip = pimary key 1) 2. each item with a item number (unique with in the slip = primary key 2) 3. the product number(s) you bought referring to the product catalog (= foreign key)) 4. (if properly done) the sales person ID (referring to the employeed table = foreign key) The real key for this table is a _concatenated key_ from 1 & 2 because this is the only unique identifier. To identify a slip select all rows with the (uniqe) slip-id. – Kurt Ludikovsky Jun 01 '15 at 09:10
0

The second way I suggest is more "standard" but since I have no idea how do you query the table and the size of it, the first method might(?) be useful

  1. concatenate all prod_id into a single string, get them using regexp

example: prod_id = 13_07_255_23_11

SELECT * FROM Sales WHERE ProductId REGEXP '(^|)23(|$)';

  1. split a single sale into multiple entries, have sales_id and product_id be your combined unique/primary key
Jacky Cheng
  • 1,536
  • 1
  • 10
  • 22