Relationships in Coldfusion.
----------------------------

Quick Version:
--------------
Relationships are a way of speeding up queries, making databases smaller and making information easier to manager, replicated data is grouped in
to a seperate table where you predict information will be used over and over again, the tutorial looks at a basic ecommerce site, showing how
to get over several barriers.  

Use The Following Code to create the relationships

<CFQUERY NAME="GetOrders" DATASOURCE="#application.dsn#">
    SELECT      *
    FROM         orders, order_items, billing, products, Categories
    WHERE       (order_items.ord_id=orders.order_id)
              AND (order_items.prod_id=products.product_id)
              AND (billing.customer_id=orders.billing_id)
              AND (products.category=categories.Category_id)
    ORDER BY    products.prod_id ASC
</CFQUERY>


This is how it will effect the following code :- 
Product Name : #product_name# (Category: #category#)

If we did that output WITHOUT the relationship it would say :-
Product Name : Black Pants (Category: 2)


If we have, in the database "Categories", a column called
'catg' next to the "Category_id"

Categories:
-----------

Catogory_id | CatG

1 Food
2 Clothes
3 Music
4 Computers

then we use this

This is how it will effect the following code :- 
Product Name : #product_name# (Category: #catg#)

If we did that WITH the relationship on, it would say :-
Product Name : Black Pants (Category: clothes)

Long Version:
-------------
When I first started using Coldfusion, I used Microsoft
Access database with one table, holding all the information,
after a while the table actually got to be a stupidly big size,
after looking at it, a lot of information was needlessly replicated

Enter the role of relationships,
at first this part of SQL territory can seem daunting and time
consuming to those who first try it, but once you get it done, you'll
be thankful for it, your database becomes smaller, your querys done
quicker, and overall administration is a lot easier.
Never underestimate good database design.

First of all, we'll take an example site
en ecommerce site, with the following tables

Billing : customer_id, name, address1, address2, City, Sate, Zip, Phone
(holds customer information for names and address's)

Products : Product_id, Product_Name, Price, Short_Description, Long_Description, Category
(holds all the different products, and descriptions,)

Categories : Category_id, Category_name
(this is the different categories the products will go into)

Orders : order_id, billing_id, Status, Date_Started, Date_finished
(this has the status details for the order, showing when it was created, when it was finished
and what the status is)

Order_items : oi_id, ord_id, prod_id
(this shows all the orders and who's ordered it)


OK, This is the Database with set up, each of the first ones, ID numbers
will be auto incremented (seed identity in SQL2000) and are primary keys

The way it works, is that we Create a relationship between two columns 
from different tables, 
ie. we have the table Categories, and the table Products,
in Categories, we have say

1. Food
2. Clothes
3. Music
4, Computers

So rather than having Products keep repeating in its Category "Computers" over and over
for all product in computers, what we do, is we just put one single number in, which
reflects the ID number in Categories.

So if the Category number in Products equals 2
then becuase of the relationship we've created, it will come back in our Query as Clothes
You can then guess how it pans out across the rest of the tables,
where in Order_items, Prod_id will equal Product_Id from the table Products etc

now for the really daunting part
Relationships in your Coldfusion query


<CFQUERY NAME="GetOrders" DATASOURCE="#application.dsn#">
    SELECT      *
    FROM         orders, order_items, billing, products, Categories
    WHERE       (order_items.ord_id=orders.order_id)
              AND (order_items.prod_id=products.product_id)
              AND (billing.customer_id=orders.billing_id)
              AND (products.category=categories.Category_id)
    ORDER BY    products.prod_id ASC
</CFQUERY>

this Query shows how to create relationships across the tables so you have 

tablename.column_name=othertablename.othercolumn_name

so anyway, if we take a look at
products.category=categories.Category_id

If we did a CFOUTPUT, on say

Product Name : #product_name# (Category: #category#)

If we did that output WITHOUT the relationship
it would return as say

Product Name : Black Pants (Category: 2)

not very productive,
but with the Relationships On?

If we have, in the database "Categories", a column called
'catg' next to the "Category_id"

Categories:
-----------

Catogory_id | CatG

1 Food
2 Clothes
3 Music
4 Computers

then we use this

This is how it will effect the following code :- 
Product Name : #product_name# (Category: #catg#)

If we did that WITH the relationship on, it would say :-
Product Name : Black Pants (Category: clothes)

tada!



About This Tutorial
Author: Alex Allen-Turl
Skill Level: Intermediate 
 
 
 
Platforms Tested: CF5
Total Views: 79,161
Submission Date: December 13, 2002
Last Update Date: June 05, 2009
All Tutorials By This Autor: 7
Discuss This Tutorial
Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.