HostMySite.Com is sponsoring this tutorial, please visit their site today!

Creating Relationships with ColdFusion by: Alex Allen-Turl

[Download in PDF Format] [Download in FlashPaper Format]

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!

HostMySite.Com is sponsoring this tutorial, please visit their site today!

This author's other tutorials:

Extensionless Coldfusion Ever wanted to be like google and run your scripts without an extension? This tutorial shows you how with Apache or IIS! - Date added: Thu. October 27, 2005 The Easiest Way to make a form! This is by far the most easiest and simplest way to make a form and update it to the database, very low maintenance and very very quick!! - Date added: Tue. December 24, 2002 Creating Dynamic Bar Charts A tutorial showing you how you can create a dynamic bar chart! - Date added: Tue. December 24, 2002 Creating Dynamic Image Galleries A tutorial showing you how you can upload one Full sized picture, and have a thumbnail automatically created for you, along with descriptions of the image! - Date added: Tue. December 24, 2002 Creating a Voting System This is a very quick tutorial on how to create a voting system for page relavances and other such things from a scale of 1 to 5! - Date added: Sat. December 21, 2002 Additional Tutorials: · Creating a Link Management System · Creating Databases in MS SQL2000

Please rate this tutorial:

Post a new comment on this tutorial
post a new comment on this particular tutorial
Your Name:
Your Email:
Comment Title:
Comments:
Key Phrase: