Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

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:

Professor
ProfessorID	Autonumber
FirstName	Text
LastName	Text
PhoneNumber	Text

Class
ClassID	Autonumber
ClassName	Text
RefNumber	Text
ProfessorID	Number

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

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