SQL Query Help - - Table Normalization

[Muzamil]

hello

I've a denormalized table PRODUCTS with following fields:
ProductNo ,
OrderNo ,
SerialNo ,
OrderDate ,
PromiseDate ,
ManufacturerID ,
..
..
..
DistributorID ,
DealerID ,
..
..
..
ReceiptDate ,
..
..
..
I have to denormalize this table, so I created 3 tables:

Table Name : ProductOrders
Fields:
+ProducrOrderID,
ProductNo ,
OrderNo ,
SerialNo ,
OrderDate ,
PromiseDate ,
ManufacturerID ,
-------------------------------
Table Name: ProductsOrdersDetails
Fields:

+ProductsOrdersDetailsID,
ProductOrdersID,
DistributorID ,
DealerID
..
..
..
-----------------------
Table Name: ProductsOrdersReceipts
Fields:
+ProductsOrdersReceiptsID,
ProductsOrdersDetailsID,
ReceiptDate ,
..
..
..

----------------------------

DistributorID and DealerID appear in Details table because a
particular order number for a specific product number can come from
different distributor and dealer.
What I need is a query to populate these normalized tables from the
original denormalized Products table.

Can any one please help?

Thanks

[Erland Sommarskog]

Muzamil (mu*****@hotmail.com) writes:

I have to denormalize this table, so I created 3 tables:

Table Name : ProductOrders
Fields:
+ProducrOrderID,
ProductNo ,
OrderNo ,
SerialNo ,
OrderDate ,
PromiseDate ,
ManufacturerID ,
-------------------------------
Table Name: ProductsOrdersDetails
Fields:

+ProductsOrdersDetailsID,
ProductOrdersID,
DistributorID ,
DealerID
.
.
.
-----------------------
Table Name: ProductsOrdersReceipts
Fields:
+ProductsOrdersReceiptsID,
ProductsOrdersDetailsID,
ReceiptDate ,
.
.
.
I think you should find the real keys of your subtables. If I understand
your narrative correctly, the primary key for ProductOrderDetails
should be (ProductOrderID, DistributorID, DealerID). In the same vein
the key for ProductsOrdersReceipts should maybe be
(ProductOrderID, DistributorID, DealerID, ReceiptDate).

In the long run this will make the data model easier to understand,
and easier to query.
What I need is a query to populate these normalized tables from the
original denormalized Products table.

Without the complete CREATE TABLE statements and sample data this
is somewhat difficult. But I would guess that for the first two
tables, you would have to use the DISTINCT keyword. But it will be
all straight-forward. At least if you follow my recommendation on the
keys.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp