What is the best way to handle (nested) record sets?

Question

Hi to all. What are the best (cleaner, less resource expensive) ways to pass one-to-many relationships from a db server to a client?

Imagine that I have a Author table and a Book table. I want to to retrieve all the authors whose name starts with "a" and all the books they've written. Then, client-side, generate an array of objects "Author" whose "Books" field is an array of objects "Books".

Two poor man solutions that come to my mind are:

1. Retrieve all the authors, cycle through them on the client and execute an additional query to get all the books
2. "SELECT a.* FROM author a, book b WHERE a.name like 'A%' and b.author_id = a.id"

The first solution is really database-side intensive (if i have 1000 authors, i have to execute 1001 queries).

The second requires some intensive work client-side, as the program would parse the result as it has the data common to Author repeated on each row.

Another solution would be to return multiple record sets from a stored procedure. I've never handled multiple record sets and I'm not sure that all the languages / adaptor classes support them.

Of course the situation can get worse if any author can have books and essays and every book can have sample pages and so on.

Any idea? Thanks

EDIT: I'm using .net, so ado's datarelations are an option. Are they supported my oracle and mysql?

Answer 1

I think the latter is probably closer to a good solution than the former.

That said, you might want to introduce a layer between the client and the database - on the database machine - that can filter and process the rows from the DB into the data structure(s) of your choosing.

Answer 2

It may depend on the choice of RDBMS, some of them may support certain feature that enables your desired function, although the feature may not be portable to another RDBMS.

As an example, Microsoft SQL Server, starting from SQL 2000, supports SELECT FOR XML. This, when the XML schema is crafted carefully (e.g. FOR XML EXPLICIT), will do exactly what you want to do -- return an XML that gives you the hierarchy of the data. If you also use ADO.NET, and choose to load it into a DataSet by using SqlDataAdaptor, the result DataSet will actually have all data already set into DataTables, with correct DataRelations set up between DataTables. However, this is probably a MS-SQL-specific feature, and working in another RDBMS may require some other techniques.

Answer 3

We use multiple recordsets. If you have another entity linked to author, just add another recordset. Not sure about Oracle and MySQL, but if corresponding implementations of IDataReader have NextResult() not throwing NotSupportedException, this should work for them.

Answer 4

Assuming a typical case, your data volume will be largely in the "Books" table. Take the simplest solution, for which the penalty is small for any display size that you would want to inflict upon a user - join the two together and return a single rowset with some amount of redundancy in "Authors" colum data. Other solutions require multiple queries, which in the overall scheme of things is in most cases both more expensive and complex.

My order of priority generally is: Deliver the content required for the best UI experience, structured for the simplest emplementation (with the normal disclaimers about unusual circumstances dictating unusual designs).

This probably also does the best job of decoupling your data strategy from changes in your UI design, because all the content is in one easily navigated result set.

Answer 5

Your two questions here. One is how to most efficiently gather the data, the other is how to deal with the data you receive.

First how to gather it:

SELECT a.* FROM Author a INNER JOIN Book b ON a.id=b.author_id WHERE left(a.name,1)='a'

Like is very expensive, avoid it if you can. Inner joins are your least expensive way to compare tables - especially if you have indexes on the Primary keys (id) and Foreign keys(author_id) If there are authors without books (wouldn't be much of an author I guess), use a LEFT JOIN (same syntax just change 'inner' to 'left')

Second. If you are talking about 1000s of records you might want to think of gathering the data in two separate pulls (like your first choice I think) unless you want the user to wait forever for the page to load. For example: the user chooses all authors that begin with a and receives a list back - you could even have a count of the articles from that author.

SELECT a.Name count(b.author_id) titles 
FROM Author a INNER JOIN Book b ON a.id=b.author_id 
WHERE left(a.name,1)='a'
GROUP BY a.Name

They'd see

John Adam:  35 Titles
Jane Acaba: 18 Titles
Jim Allan:  3 Titles

Then the user clicks on the author, load the list of books for that author.

Jim Allan's Titles:
   Froggy went a court'n
   Death on the Nile
   Life in Africa

If you want it to appear like it's all done with one pull, but you want it to come over really fast use xmlhttp or ajax to show the list of authors. That is a whole other topic. :)

I also should add that stored procedures are a must. The will add significantly to the portability of the app as well as to the performance in most databases.

HTH