When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Saturday, September 25, 1999

Data Shaping


Relational databases, while easy to create, can sometimes be hard to query, especially when you have related tables. Imagine that in your database you had two tables: Class and Professor. The Class table contains a listing of classes offered, while the Professor table lists the professors at the university. Obviously there is a relation between these two tables: classes are taught by professors, so, when designing our database, we'd definately have a 1-to-many relationship between these two tables. (One professor can teach many classes...). Here are the table definitions of both Professor and Class:

- continued -

Professor
ProfessorIDAutonumber
FirstNameText
LastNameText
PhoneNumberText

Class
ClassIDAutonumber
ClassNameText
RefNumberText
ProfessorIDNumber

Note the ProfessorID primary key in the Professor table, and how it is related to the Class table.

While this relational design allows for scalability, it makes it a bit difficult to obtain information from both of these tables. Assume that we wanted to list all of the professors, and what classes they taught. We would have to use a JOIN, like so:

SELECT ProfessorID, FirstName, LastName, ClassName
FROM Professor P
   LEFT JOIN Class C ON
      C.ProfessorID = P.ProfessorID

We'd then step through our recordset displaying the professor's name and classes taught. Using some sample data, stepping through this recordset yielded:

1	Dr.	Smith		Analysis of Algorithms
1	Dr.	Smith		Artificial Intelligence
2	Dr.	Johnson		Distributed Systems
3	Dr.	Mitchell	Formal Languages & Automata Theory
3	Dr.	Mitchell	Study of Film
3	Dr.	Mitchell	Software Engineering
(Yes, I entered Dr. as the FirstName for all of the professors!) You can see from the sample data that we have three professors, Dr. Smith, Dr. Johnson, and Dr. Mitchell. Dr. Smith teaches two classes, Dr. Johnson one, and Dr. Mitchell three.

Now, assume that we wanted to list all of the classes, and what professor taught it (as opposed to listing the professors, and what classes they taught). We'd just need to modify our SQL statement, but again, we're stuck with a nasty JOIN.

SELECT ClassID, ClassName, FirstName, LastName
FROM Class C
   INNER JOIN Professor P ON
      P.ProfessorID = C.ProfessorID

We'd then step through our recordset displaying the class's name and the professor that taught the class. Using our sample data, stepping through this recordset yielded:

1	Analysis of Algorithms			Dr.	Smith
2	Artificial Intelligence			Dr.	Smith
3	Distributed Systems			Dr.	Johnson
4	Formal Languages & Automata Theory	Dr.	Mitchell
5	Study of Film				Dr.	Mitchell
6	Software Engineering			Dr.	Mitchell
While using JOINs is an acceptable method of grabbing the related data, we can use what is called Data Shaping to make our lives much easier! Part 2 describes what data shaping is and how we can use it to retrieve related data!

  • Read Part 2


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article