Cursor with categories and quantity of each category 2

[SitesMasstec]

My table ESTOQUE.DBF (Products) which has a field - Eforn - which stores the Supplier name.

To get all supplier names I use the command:

SELECT DISTINCT Eforn FROM Estoque ORDER BY Eforn INTO CURSOR curFornece

It produces in the cursor:
ALLISTAIR MARKET
BERT INDUSTRIAL
DAWSON & SONS
etc...
This is OK.


But is it possible to, besides get all suppliers names (above), to have also the quantity of products (records in Estoque.DBF) from each supplier, without having to create another cursor or temporary file? For example:
ALLISTAIR MARKET 13
BERT INDUSTRIAL 5
DAWSON & SONS 20
etc...


Thank you,
SitesMasstec

 

[Chris Miller]

First of all, you should have a supplier table. Then ESTOQUE.EFORN should become ESTOQUE.EFORNID and point to an id in the new supplier table.

But assume you don't change that right now, you can use a GROUP BY, whether it groups by EFORN or EFORNID wouldn't matter, by this grouping you can then also use COUNT() to get the counts you want at the same time when creating the list of suppliers, which you actually don't need to build up every time as they should be a table of their own anyway.

Well, the query for a GROUP BY is

SELECT Eforn, COUNT(*) as ProductCount FROM Estoque GROUP BY Eforn INTO CURSOR curFornece

Chriss

 

[Chris Miller]

I translated Estoque and it means stock.

Stock is not the same as products, unless you use the products table for maintaining the stock you have of products. That's not wrong, even by data normalization rules, but you muddle things together that make data less concise. If you have productname, stock, suppliername as combination of fields, take aside the problem of cases where you have a typo in one of the names, that's a problem of clean data. Even assuming you don't have that, the actual stock would need to sum the stocks of same product independent of supplier and so you don't have a concise stock of a product, you neither have clear unique product list, as a product you have in stock from different suppliers must be listed in it twice or more times, nor do you have a clear unique supplier lit, as each supplier supplies multiple products.

And that would also concern the count of distinct products. Say you have a stock of a product ordered last year and still not completely sold and already another stock record of a delivery from the same supplier for the same product, then the query I gave you would count that twice, even though it's the same product/supplier combination.

So if you have a productid or product name field in the table, lets say its name is produto, then the actual count of products you get per suppliers rather is

SELECT tmp.Eforn, COUNT(*) as ProductCount FROM (Select Distinct Eforn, Produto From Estoque) as tmp GROUP BY tmp.Eforn INTO CURSOR curFornece

The inner Select Distinct Eforn, Produto From Estoque would then list all the distinct combinations of product and supplier, first.

The previous simpler query SELECT Eforn, COUNT(*) as ProductCount FROM Estoque GROUP BY Eforn INTO CURSOR curFornece is not giving you the number of products you have in stock from a supplier, but count any stock you have not cumulated as a separate product, which it wouldn't necessarily be.

If you don't normalize your data you will always have problems of such nature, that will not become obvious.

More than that, a stock table will surely not store a product you don't have ordered from a supplier, but which a supplier could supply, so you only have a count of products you ordered from a supplier, not the count of products you could order from a supplier. That may be nowhere in your database but would be worth having a table for, like supplierproducts - all products a supplier can supply, which would become your source for gettint the count of products a supplier offers.

So all in all, data normalization is important to have your business statistics correct and be able to make the right business decisions, i.e. how much to restock from which supplier. That would also need to take into account sales figures and projected sales. You're not getting this from that question alone, but even less so from data that's not normalized.

Chriss

 

[SitesMasstec]

Nice tips about data normalization, Chris, thank you.

I use the ESTOQUE.DBF table to store all produts in stock. Each product has a name (field ENOME), a unit (field EUNID), among others. the prefix 'E' is the first letter I use in all fields of the ESTOQUE.DBF table.

Thank you,
SitesMasstec

 

[Chris Miller]

ESTOQUE.DBF table> all produts in stock.

Well, then I have a few questions.

1. What about products not in stock?
2. What about products you have in stock from two or more suppliers?
3. What about suppliers that didn't supply to the current stock?

Chriss

 

[SitesMasstec]

Here as the answers, Chris:

1. What about products not in stock?
- Produts that are not in stock, have their quantity (field EQTDE) equal to 0(zero).

2. What about products you have in stock from two or more suppliers?
- There is a field for the supplier (field EFORN) which supports 18 characters (you can abreviate suppliers'names when more than one).

3. What about suppliers that didn't supply to the current stock?
- I have a report which shows which products have to be ordered. It can be generated every day or every week. If the registered supplier cannot attend the order, the person responsible for buying products have to call others suppliers. The same occur if the registered supplier offers product with a high price. The buyer sometimes can search for other suppliers and change the supplier/s for that product.


Thank you,
SitesMasstec

 

[Chris Miller]

You misunderstood question 3.

But your answer to question 2 poses a new problem that wasn't accounted for even in the extended query I gave you. Well, well.

First, again question 3: As you take you supplier list from ESTOQUE.DBF with your previous query SELECT DISTINCT Eforn FROM Estoque ORDER BY Eforn INTO CURSOR curFornece, that wouldn't include suppliers for which there is no ESTOQUE record, wouldn't it? You said you include records with a stock count of 0, but those records: How would they store a supplier of 0 items?

4. Do you also have a separate list of products and of suppliers in your data?

And back to the new "fire" you started:
If you can have more than one supplier in ESTOQUE.EFORN how can you expect a query to separate them? You can't get a good count per supplier, if in some cases you even have multiple suppliers. The query as given would count pairs or triples of suppliers stored in some cases as one supplier separate from each of the single suppliers. That's even worse than anything I assumed could have gone wrong.

Chriss

 

[SitesMasstec]

Just to be clear, Chris: my application does not have a table for suppliers. So I could not have a suplier's name in a report, for suppliers that do not have products in stock.

Of course, this application fails in the case the user types the supplier 'DAWSON & SONS' in a product record and in another record he/she types 'DAWSON & SON'. In this case, there will be 2 different suppliers'names in the Suppliers report...

Thank you,
SitesMasstec

 

[Chris Miller]

...'DAWSON & SONS' in a product record and in another record he/she types 'DAWSON & SON'.

Yes, you recognize one problem, but do you ignore what I said last, it's based on what you said and is a far heavier problem than that. You said:

you can abreviate suppliers'names when more than one

That means when you do the query I gave you, you get a count of a supplier that isn't a supplier, but two or more suppliers squeeezed into this one field. All abbreviations are also counting as their own name and will not merge with the full name of a supplier. So the first step would actually be to create a full list of suppliers. Im the cases you have more than one supplier I assume the total stock then was partly from one and partly from the other supplier, do you have records of deliveries that lead to the stock you have? Because that would at least be a better source of data, there wouldn't be a delivery that comes from two or more suppliers at the same time, would there?

Well, you can't make sense of nonsensical data. Your result will be good for many suppliers, you may cope with the records that have two or more and abbreviated names on your own, but that means you accept to need to do manual work, over and over again?

Don't get me wrong, even in a better normaliuzed database the data can be off reality, people tend to have a backlog of things not yet entered into the database, for example, but this is not a good example of the 80/20 rule that you have a good enough database to work with. I say even assuming you work with that data for years, if not even decades and improve it from time to time in aspects you notice need improvement. Well, yu have a very good example of data gone quite wrong, here. It's a bit like having preprinted forms with certain fields and making manual entries into that, using the page margin for "extra data" to be better then the planned preprinted columns. This may all not be business critical, if a rough overview is good enough for you, but it's clearly something that could have been done far better. And the limited description you gave initially leads to a starred answer (even twice, by someone else than you, too) that's actually not working, considered all we learned about your table by now.

Edit: All that said, the result you get with the simple group by query will be good for many suppliers, I guess. It depends on how many times more than one supplier is stored into that field. You'll likely find the count for those combinations of suppliers at the low count end, when sorting by the count column and then you can either statistically negelect those or add them to the full name supplier, maybe split up between two or more. If, on the other hand most products come in from multiple suppliers, that'll dominate the counts and you won't get a clear picture for the single suppliers.

Chriss

 

[mjcmkrsr]

Hi SMT,

Of course, this application fails in the case the user types the supplier 'DAWSON & SONS' in a product record and in another record he/she types 'DAWSON & SON'.

You definitely need a suppliers table presented to the user in a way he/she may pick from a list and only add a new supplier if the one searched for is not found. Please reread the thread about incremental search - thread184-1830298.

hth

MarK

 

[mjcmkrsr]

... and a rough sketch of how your could approach the supplier - product - stock problem

LOCAL loForm

loForm = NEWOBJECT("clsForm")
loForm.Visible = .T.
loform.Show

READ Events

CLOSE ALL
CLEAR ALL 

RETURN

**********

DEFINE CLASS clsForm AS form
Height = 720
Width = 720
Autocenter = .T.
Borderstyle = 2
MinButton = .F.
MaxButton = .F.
Themes = .F.
ShowTips = .T.
Caption = "Suppliers"

DIMENSION laSuppliers[1]
	
	ADD OBJECT lblName as Label WITH ;
		Top = 24, ;
		Left = 24, ;
		AutoSize = .T. , ;
		Caption = "Supplier Name"
	
	ADD OBJECT lblAddress as Label WITH ;
		Top = 24, ;
		Left = 24 + 120 + 12, ;
		AutoSize = .T. , ;
		Caption = "Adresse"
	
	ADD OBJECT lblCity as Label WITH ;
		Top = 24, ;
		Left = 156 + 120 + 12, ;
		AutoSize = .T. , ;
		Caption = "City"
	
	ADD OBJECT lblSearchSupplier as Label WITH ;
		Top = 24, ;
		Left = 420, ;
		AutoSize = .T. , ;
		Caption = "Search Supplier Name"

	ADD OBJECT lblProduct as Label WITH ;
		Top = 90, ;
		Left = 24, ;
		AutoSize = .T. , ;
		FontBold = .T. , ;
		Caption = "Supplier - Product - Stock location"
	
	ADD OBJECT lblInSupStock as Label WITH ;
		Top = 420, ;
		Left = 24, ;
		AutoSize = .T. , ;
		FontBold = .T. , ;
		Caption = "Product - Supplier - In Stock (by Supplier)"
	
	ADD OBJECT lblInProStock as Label WITH ;
		Top = 420, ;
		Left = 366, ;
		AutoSize = .T. , ;
		FontBold = .T. , ;
		Caption = "Product - Supplier - In Stock (by Product)"
	
	ADD OBJECT txtName as TextBox WITH ;
		Top = 48, ;
		Left = 24, ;
		Width = 120, ;
		ControlSource = "Suppliers.cName", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
		
	ADD OBJECT txtAddress as TextBox WITH ;
		Top = 48, ;
		Left = 24 + 120 + 12, ;
		Width = 120, ;
		ControlSource = "Suppliers.cAddress", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)

	ADD OBJECT txtCity as TextBox WITH ;
		Top = 48, ;
		Left = 156 + 120 + 12, ;
		Width = 120, ;
		ControlSource = "Suppliers.cCity", ;
		ReadOnly = .T., ;
		DisabledBackColor = RGB(0, 240, 240)
			
	ADD OBJECT cboSuppliers as ComboBox WITH ;
		Style = 2, ;
		Top = 48, ;
		Left = 420, ;
		Width = 252, ;
		RowSourceType = 5, ;
		RowSource = "ThisForm.laSuppliers", ;
		ColumnCount = 2, ;
		ColumnWidths = "228, 0", ;
		IncrementalSearch = .T.
		
		PROCEDURE cboSuppliers.GotFocus()
		
*!*			You don't want to have too many items in the COMBOBOX hence you might want to filter with WHERE 
		
			Select ALLTRIM(cName) ;
				FROM Suppliers ;
				WHERE .T. ;
				ORDER BY 1 ;
				INTO ARRAY ThisForm.laSuppliers
				
*!*				DIMENSION ThisForm.laSuppliers[ALEN(ThisForm.laSuppliers) + 1]

*!*				WITH ThisForm
*!*					.laSuppliers[ALEN(ThisForm.laSuppliers, 1)] = "*** Record not found ***"
*!*				ENDWITH 

			This.Requery()

		ENDPROC 
		
		PROCEDURE cboSuppliers.LostFocus()
		
			This.Click()
			
		ENDPROC 
		
		PROCEDURE cboSuppliers.Click()
			LOCAL lcSupplierName as character  
			
			lcSupplierName = This.List[This.Listindex, 1]

			IF SUBSTR(ALLTRIM(lcSupplierName), 1, 3) = "***"
			
				IF MESSAGEBOX("Do you want to add a record?", 4 + 32, "Add Record") = 6
					WAIT WINDOW + "Your code to add a new record" TIMEOUT 3

				ENDIF 
			ELSE 
			
*!*			You may SEEK or INDEXSEEK in a large and indexed table - in a small table LOCATE is fine

				SELECT Suppliers 
	
				LOCATE FOR Suppliers.cName = lcSupplierName 

				Thisform.Refresh()
				
				SELECT cName, cPName, dStockedIn, iQuantity, cSTLocation, iFloor, iRoom ;
					FROM Suppliers ;
						JOIN Products ON Suppliers.iSID = Products.iSId ;
						JOIN StockLocation ON Products.iSTId = StockLocation.ISTId ;
					WHERE cName = lcSupplierName ;
					ORDER BY 1,2 ;
					INTO CURSOR csrStock
				
				WITH ThisForm.grdProducts
					.RecordSource = "CSRSTOCK"
					.Visible = .T.
					.Refresh()
				ENDWITH 
				
				SELECT cPName, cName, SUM(iQuantity) ;
					FROM Products ;
						JOIN Suppliers ON Suppliers.iSID = Products.iSID ;
					GROUP BY 1, 2 ;
					ORDER BY 2, 1 ;
					INTO CURSOR csrInSupStock
				
				WITH ThisForm.grdSupStock
					.RecordSource = "CSRINSUPSTOCK"
					.Visible = .T.
					.Refresh()
				ENDWITH 
				
				SELECT cPName, cName, SUM(iQuantity) ;
					FROM Products ;
						JOIN Suppliers ON Suppliers.iSID = Products.iSID ;
					GROUP BY 1, 2 ;
					ORDER BY 1, 2 ;
					INTO CURSOR csrInProStock
				
				WITH ThisForm.grdProStock
					.RecordSource = "CSRINPROSTOCK"
					.Visible = .T.
					.Refresh()
				ENDWITH 
			ENDIF 
		ENDPROC 
		
	Add Object grdProducts as Grid with;
		Top = 114, ;
		Left = 24, ;
		Height = 300, ;
		Width = ThisForm.Width - 48, ;
		AllowRowSizing = .F., ;
		DeleteMark = .F., ;
		Visible = .F., ;
		Anchor = 15, ;
		ReadOnly = .T., ;
		ColumnCount = 7
		
		PROCEDURE grdProducts.Refresh()
			WITH This 
				.Column1.Header1.Caption = "Supplier"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 120

				.Column2.Header1.Caption = "Product"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Date"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 90

				.Column4.Header1.Caption = "Quantity"
				.Column4.Header1.FontBold = .T.
				.Column4.Width = 48

				.Column5.Header1.Caption = "Location"
				.Column5.Header1.FontBold = .T.
				.Column5.Width = 90

				.Column6.Header1.Caption = "Floor"
				.Column6.Header1.FontBold = .T.
				.Column6.Width = 48
				
				.Column7.Header1.Caption = "Room"
				.Column7.Header1.FontBold = .T.
				.Column7.Width = 48
			ENDWITH 
		ENDPROC 		


	Add Object grdSupStock as Grid with;
		Top = 438, ;
		Left = 24, ;
		Height = ThisForm.Height - 438 - 24, ;
		Width = 330, ;
		AllowRowSizing = .F., ;
		DeleteMark = .F., ;
		Visible = .F., ;
		Anchor = 15, ;
		ReadOnly = .T., ;
		ColumnCount = 3
		
		PROCEDURE grdSupStock.Refresh()
			WITH This 
				.Column1.Header1.Caption = "Product"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 120

				.Column2.Header1.Caption = "Supplier"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Qty"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 48
			ENDWITH 
		ENDPROC 

	Add Object grdProStock as Grid with;
		Top = 438, ;
		Left = 366, ;
		Height = ThisForm.Height - 438 - 24, ;
		Width = 330, ;
		AllowRowSizing = .F., ;
		DeleteMark = .F., ;
		Visible = .F., ;
		Anchor = 15, ;
		ReadOnly = .T., ;
		ColumnCount = 3
		
		PROCEDURE grdProStock.Refresh()
			WITH This 
				.Column1.Header1.Caption = "Product"
				.Column1.Header1.FontBold = .T.
				.Column1.Width = 120

				.Column2.Header1.Caption = "Supplier"
				.Column2.Header1.FontBold = .T.
				.Column2.Width = 120

				.Column3.Header1.Caption = "Qty"
				.Column3.Header1.FontBold = .T.
				.Column3.Width = 48
			ENDWITH 
		ENDPROC
		 
	PROCEDURE Load
		CREATE CURSOR Suppliers (iSID I AUTOINC NEXTVALUE 10, CName C(42), cAddress C(42), cCity c(42))
		
		INSERT INTO Suppliers (cName, cAddress, cCity) values("Desmarets & Fils", "12 Grand-Rue", "1235 Gent")
		INSERT INTO Suppliers (cName, cAddress, cCity) values("Deutz & Deutz", "112 Gehlach Allee", "15235 Munich")
		INSERT INTO Suppliers (cName, cAddress, cCity) values("Los Olivos", "564 Calle Grande", "65235 Barcelona")
		INSERT INTO Suppliers (cName, cAddress, cCity) values("Veuve Cliquot", "12, rue de Champs", "62254 Epernay")
		INSERT INTO Suppliers (cName, cAddress, cCity) values("Caves Chablis", "54, rue des Vignes", "61546 Chablis")
		
		LOCATE 

		CREATE CURSOR Products (iPID I AUTOINC NEXTVALUE 100, CPName C(42), dStockedIn D, iQuantity I, iSID I, iSTID I)
		
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Champagne 75 cl", DATE() - INT(RAND() * 60), 5, 11, 1000)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Champagne 75 cl", DATE() - INT(RAND() * 60), 15, 11, 1000)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Champagne 150 cl", DATE() - INT(RAND() * 60), 5, 11, 1001)

		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Champagne 150 cl", DATE() - INT(RAND() * 60), 30, 13, 1001)

		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Olive Oil 75 cl", DATE() - INT(RAND() * 60), 5, 12, 1003)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Olive Oil 75 cl", DATE() - INT(RAND() * 60), 25, 12, 1003)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Olive Oil 75 cl", DATE() - INT(RAND() * 60), 35, 12, 1003)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Green Olives 500 gr", DATE() - INT(RAND() * 60), 25, 12, 1004)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Black Olives 250 gr", DATE() - INT(RAND() * 60), 25, 12, 1004)

		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Beer Pilsen 33 cl", DATE() - INT(RAND() * 60), 24, 10, 1002)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Beer Pilsen 33 cl", DATE() - INT(RAND() * 60), 48, 10, 1002)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Beer Blonde 33 cl", DATE() - INT(RAND() * 60), 24, 10, 1002)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Beer Blonde 33 cl", DATE() - INT(RAND() * 60), 12, 10, 1002)
		INSERT INTO Products (cPName, dStockedIn, iQuantity, iSID, iSTid) VALUES ("Beer Brown 33 cl", DATE() - INT(RAND() * 60), 24, 10, 1002)

		CREATE CURSOR StockLocation (iSTID I AUTOINC NEXTVALUE 1000, CSTLocation C(42), iFloor I, iRoom I)

		INSERT INTO StockLocation (cSTLocation, iFloor, iRoom) values("Main Building", 1, 101)
		INSERT INTO StockLocation (cSTLocation, iFloor, iRoom) values("Main Building", 1, 102)
		INSERT INTO StockLocation (cSTLocation, iFloor, iRoom) values("Main Building", 1, 103)
		INSERT INTO StockLocation (cSTLocation, iFloor, iRoom) values("Main Building", 2, 201)
		INSERT INTO StockLocation (cSTLocation, iFloor, iRoom) values("Main Building", 2, 202)
		INSERT INTO StockLocation (cSTLocation, iFloor, iRoom) values("Main Building", 2, 203)

		SELECT Suppliers
		
	ENDPROC 

	PROCEDURE Destroy
		ThisForm.Release()
		CLEAR EVENTS

	ENDPROC 
ENDDEFINE

**********

hth

MarK

 

[Chris Miller]

To say something about the data structuring:

That you cramp abbreviations of suppliers into a char(18) suppliername field when there were more than 1 supplier adding to the product stock, then that's a sign to do something. You could have at least extended the field to char(100) or made a memo of it, even if you're not familiar with database normalization rules. You are not limited, but it seems you're quite passive.

In the aspect of stock I said it's opkay to have the stock amount like an item count or also a weight or volumne of products within the products table, but stock indeed is a topic that's a bit more than that. Here you wanted to get an answer to the question which products your supplier can deliver.

Well, there would be a more appropriate table for that, a table of deliveryitems in which you record each delivery in several records, one per product with the count, weight, volume or length of what was delivered by product specification. I already hinted you better have a units table for that matter and each product can point out its unit. With such a deliveryitem table you wouldn't have a single count/measure per product but a number of records, each with their own supplier, usually also only stored as ID of both product and suppplier. Which makes a deliveritem an example of one of the most unintuitive tables in a datbase: A cross reference table.

Unintuitive, that is as long as you don't realize the power of the concept ot IDs. It is the most simple aspect to learn about databases, even aside of the aspect of normlaization, you always only store things like a supplier name, proiduct name, etc. in their "home" table and everywhere else in your database where you want to store an item like a product, a supplier, a person, a location, whatever else - gets represented by its ID only.

A delivery would actually be just the same as an order, just with a slightly different perspective on the same thing, you have a main deliver record like you have an order record and deliveritems are the same as orderitems, just your shop system is the "customer" in this case and your orders are for your stock and store houses or shops and nopt in a shopping cart (literally or virtual) of an end customer.

And then you do have orders and orderitems of your customers and the actual stock is not necessarily stored anywhere, it computes as the difference of items delivered to you and shipped to customers. One way to still have a stock figure in your proeducts is to have it as redundancy you intentionally store, that comes with the simple business logic of a) increasing the stock figure with each delivery and b) decresing it with each orderitem shipping. You will then have the possibility that this stock figure differs from the real stock, as this schema doesn't yet account for losses like stolen products, expired food, etc. This and other reasons speak for a dedicated storage of such a stock figure that can differe from the logical difference of bought (or even self produced) and sold products. Such stock corrections could also be stored in their own table, to have an overview about that and more data about business decisions like not having too high stock of perishable goods.

Let's set what I said in stone and you'd have these tables:
Suppliers, Deliveries, Deliveryitems, Customers, Orders, Orderitems, Products, Units.

To get your list of suppliers and product count you'd then only need to query Deliveryitems, each deliveryitem would store a productid and supplierid and more fields, which would not be of interest in this query. The topic of stock corrections would also not matter, as you're only need the answer to the question which suppliers deliverd which number of different products to you in the past, i.e. with all deliveries to you, that you recorded. And that would then be

Select Supplierproducts.SupplierID, Count(*) From (Select Distinct SupplierID, ProductID from DeliveryItems) as Supplierproducts Group by Supplierproducts.SupplierID

And then there is the aspect I also already hinted about: You could have a Supplierproducts table instead of the Supplierproducts subquery I used here that condenses the information of Deliveritems. Because it could pay to have that as extra data. Sounds like a lot of redundancy, but it would not only record the products you got delivered from suppliers, but all products (you know of) a supplier could possibly deliver. Such a table would have the advantage of being current, i.e. what a supplier deliverd 20 years ago may not be in his offered goods anymore. You could store and keep prices up to date, you could store things like a minimal order for discounts, etc., so it's worth having a Supplierproducts table that's mainly storing - hopefully obvious by now, pairs of supplierids and productids, a cross reference table. As said, further columns could store things like the current price, etc.

And that would be the final answer to your original question. It's not true for any statistical list you might need at some time, you may find it over the top to list all products a supplier could deliver as you're not interested in all products, but by all means, you don't have to insert into such a table what's of no interest, i.e. the Suopplierproducts list could be incomplete from the point of view of the supplier, who could also deliver things you don't ever order. Your database is only about your concerns of your perspective.

Nevertheless, don't lose sight of the abilities you get by storing data in the best suited way to not only answer one, but any question. I could end here, but that's a bit cryptic, isn't it? Well, to get back to the most commonly known example of the simple normalizaiton of orders, the multiple orderitem records don't just allow to see which items belong to an order, the orderitems tbale also can answer questions like how many times a product was ordered, by not limiting the orderitem list to items of the same orderID, but picking out all orderitems with the same productid. And that gives you a glimpse of how Ids help to manage data in a better processible way.



Chriss