Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Here is an Example of Normalizing Tables in a Script. I advise you do something like this</p> <pre><code>e.g Table: tbl_tmpData Date, ProductName, ProductCode, ProductType, MarketDescription, Units, Value 2010-01-01, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 1', 20, 20.00 2010-01-02, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 2', 40, 40.00 2010-01-03, 'Arnotts Biscuits', '01', 'Biscuit', 'Store 3', 40, 40.00 2010-01-01, 'Cola', '02', 'Drink', 'Store 1', 40, 80.00 2010-01-02, 'Cola', '02', 'Drink', 'Store 2', 20, 40.00 2010-01-03, 'Cola', '02', 'Drink', 'Store 2', 60, 120.00 2010-01-01, 'Simiri Gum', '03', 'Gum', 'Store 1', 40, 80.00 2010-01-02, 'Simiri Gum', '03', 'Gum', 'Store 2', 20, 40.00 2010-01-03, 'Simiri Gum', '03', 'Gum', 'Store 3', 60, 120.00 </code></pre> <p>You would Create Your Date Table first:</p> <pre><code>CREATE TABLE tbl_Date ( DateID int PRIMARY KEY IDENTITY(1,1) ,DateValue datetime ) INSERT INTO tbl_Date (DateValue) SELECT DISTINCT Date FROM tbl_Data WHERE Date NOT IN (SELECT DISTINCT DateValue FROM tbl_Date) </code></pre> <p>you would then Create your Market Table</p> <pre><code>CREATE TABLE tbl_Market ( MarketID int PRIMARY KEY IDENTITY(1,1) ,MarketName varchar(200) ) INSERT INTO tbl_Market (MarketName) SELECT DISTINCT MarketDescription FROM tbl_tmpData WHERE MarketName NOT IN (SELECT DISTINCT MarketDescription FROM tbl_Market) </code></pre> <p>you would then Create your ProductType Table</p> <pre><code>CREATE TABLE tbl_ProductType ( ProductTypeID int PRIMARY KEY IDENTITY(1,1) ,ProductType varchar(200) ) INSERT INTO tbl_ProductType (ProductType) SELECT DISTINCT ProductType FROM tbl_tmpData WHERE ProductType NOT IN (SELECT DISTINCT ProductType FROM tbl_ProductType) </code></pre> <p>you would then Create your Product Table</p> <pre><code>CREATE TABLE tbl_Product ( ProductID int PRIMARY KEY IDENTITY(1,1) , ProductCode varchar(100) , ProductDescription varchar(300) ,ProductType int ) INSERT INTO tbl_Product (ProductCode, ProductDescription, ProductType) SELECT DISTINCT tmp.ProductCode,tmp.ProductName, pt.ProductType FROM tbl_tmpData tmp INNER JOIN tbl_ProductType pt ON tmp.ProductType = pt.ProductType WHERE ProductCode NOT IN (SELECT DISTINCT ProductCode FROM tbl_Product) </code></pre> <p>you would then Create your Data Table</p> <pre><code>CREATE TABLE tbl_Data ( DataID int PRIMARY KEY IDENTITY(1,1) , DateID varchar(100) , ProductID varchar(100) , MarketID varchar(300) ,Units decimal(10,5) , value decimal(10,5) ) INSERT INTO tbl_Data (ProductID, MarketID, Units, Value) SELECT t.DateID , p.ProductID , m.MarketID , SUM(tmp.Units) , SUM(tmp.VALUE) FROM tbl_tmpData tmp INNER JOIN tbl_Date t ON tmp.Date = t.DateValue INNER JOIN tbl_Product p ON tmp.ProductCode = p.ProductCode INNER JOIN tbl_Market m ON tmp.MarketDescription = m.MarketName GROUP BY t.DateID, p.ProductID, m.MarketID ORDER BY t.DateID, p.ProductID, m.MarketID </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to normalize a SQL Database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBobson
    1. USRobbie Tapping
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to normalize a SQL Database
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONo Problems at all mate! I highly advice writing a script. in the fashion above rather than trying to creat Views.
      singulars
      1. PO
      1. USRobbie Tapping
    2. COOkay, this is the direction I'm wanting to head, but what about if the primary key changes? For example, in my case, my 1 table is essentially split into 2. Another table depended on the original table before the split, and now is dependent on the 2 table in the split.
      singulars
      1. PO
      1. USLordZardeck
    3. CO@LordZardeck, Could you Post some examples of the Tables you want to normalize, and ill see what i can do to put you in the right direction.
      singulars
      1. PO
      1. USRobbie Tapping
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload