Sql Database Table Design - Designing a SQL database
using Microsoft SQL Server 2005

  
  By Granville Stewart  
       
  When creating a SQL database you need to ask - what is the information that I want to capture? Do not try to capture everything on one SQL database. Simplify it. It is OK to create several SQL databases with specific types of information you want to capture for an organization. You can always create a sql command to gather the information from the separate databases.

Afterwards, you can get down to designing the TABLES. Below are three rules about SQL Table design. I know that rules are made and then broken, but in general follow the 3 Rules below (if you can).		    
       
Three Basic
SQL Table Design Rules.
 RULE 1:  In a Table, do not repeat data.

RULE 2:  Repeated data goes in another Linked table.

RULE 3:  Row Data must only rely on the Table's Primary Key.
    
     
     
  I created this table below: Tables: CUSTOMERS - DIST - INVOICE - PAPER - SECONDADDRES
Click BELOW for larger image of Primary Key and Foreign Key relationships. Remember, a Foreign Key is just another name for a Primary Key in another table that you are Linking to (Rule 2).		  
 
 
 
 
     
 
  Now I select six columns from the CUSTOMERS TABLE to demonstrate. Below is a data grid from Visual Web Developer 2005 Express Edition - which uses the SQL Data Sources to display the data on a web page that exist within the database. This is not an article on Visual Web Developer 2005 Express Edition or Visual Basic, so we will explain how to get data directly using SQL statements. How you display the data to the customer be it Web Based or Application base is a different subject.  
   
  OK - Now that we have our tables set up - time to run some SQL statements directly from the Database. This is by no means a complete manual - I have included sql links at the end for you to learn sql from other sources.  
SQL
STORED
PROCEDURES
Statement:
    
  First, how to create STORED PROCEDURES. This is so we do not have to keep writing the same SQL statements over and over. Instead we replace the @ variables to get our data.
EXAMPLE:
  
     
SQL
CREATE PROCEDURE
Statement:
 CREATE PROCEDURE Cust_IDMetro
@Cust_ID INT
@Zip INT
AS
SELECT Cust_ID, LastName, Phone, Zip
  
     
  Below is how we would execute the stored procedure named Cust_IDMetro  
     
Execute
(Procedure)
Cust_IDMetro
Like so...
 FROM Customers
WHERE Cust_ID = @Cust_ID
AND
Zip = @Zip
  
     
  Ok, did we understand the Above? See below for more examples of SQL querys.  
     
Question:  
 

There are two tables. Table one named CUSTOMERS.
With two columns, named LastName and FirstName.

Table Two is called COMPLAINTS.
With two columns, named LN and FN.

 

    
  How do you return the Last and First Names from the CUSTOMERS table
that do not appear in the  LN and FN Columns in the COMPLAINTS table?		  
     
Answer: use the EXCEPT statement as shown below.  
SQL
EXCEPT
Statement:
    
  SELECT LastName, FirstName
FROM Customers
EXCEPT
SELECT LN, FN
FROM Complaints;
  
SQL
JOIN
Statement
    
 

JOINS

NATURAL JOIN Returns rows between Tables sharing the same COLUMN NAMES.
This is fine when two tables share one similar COLUMN NAME.

But, if both Tables share many similar COLUMN names, for example both Tables have 'LastName', 'FirstName' and 'Phone' Columns, then SQL will return results from those 3 similar Columns. Usually not desired. Similar to a Cartesian Product but limited to 3 rows instead of every row.

So instead, use JOIN and specify where you want the Tables to JOIN with an ON clause.

 

 

  
SQL
INNERJOIN
Statement
    
  SELECT C.LastName, C.FirstName,
I.Invoice_ID, I.Cust_ID
FROM Customers C  JOIN Invoice I
ON C.Cust_ID = I.Cust_ID
WHERE C.LastName = 'Stewart';
  
SQL
Sub Query
Statement
    
 

Sub Query

Why do you still need the Sub Query when the SELF JOIN with an Alias specifying the same Table does the same thing?

(Example SELECT X,Y,Z FROM Employees E , Employees EM... ) Where the 'E' and 'EM' are Alias for the same table named Employees.

One Word, Visual Basic 2005, OK two words and a number.

Need Sub Query to specify specifically where sql will take a result and pass it back to the main query.

EXAMPLE

 
     
 
SELECT Paper_ID
FROM Paper
WHERE Paper_ID IN
  (SELECT Paper_ID FROM Invoice
WHERE Cust_ID = @Cust_ID);		  
     
The Sub Query is in the ( ..... )
  
     
  So there you go. Some samples of SQL statements and rules on how to design your database. And remember your best database is always going to be your next project. There is no end to the learning curve in the wonderful world of Databases. This is by no means a complete manual - I have included sql links at the end for you to learn sql from other sources.  
     
     
Granville Stewart is an IT professional in Las Vegas and is a contributor to LVITweb.com  
  Find more informative articles on LVITweb.com  
     
     
  For More Information  
  SQL.org aims to be both a portal to SQL resources on the internet, and a source of original SQL-related content.  
http://www.sql.org/  
  The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s.  
http://en.wikipedia.org/wiki/SQL  
  Visit this portal for all the latest news and articles to help you evaluate Microsoft SQL Server 2005 for your data management and analysis needs.  
http://www.microsoft.com/sql/default.mspx