One-to-many relationship in asp 3.0 object design

[Jon Maz]

Hi All,

I need some help optimising my object design. Since one article has 1+
authors, I assume the logical way to represent this is for my Article object
to have a Authors property, of type "array", capable of representing any
number of authors.

The code I have written (below) works, but is very inefficient; if I want
the asp page to fetch a list of 30 authors using GetAuthors(30), there will
be a total of *31* calls to the database - one call in GetArticles(), and
one call to GetAuthors() for EACH article!

This must be a common situation, and I'd be grateful for any strategies for
dealing with this. Any help much appreciated!

TIA,

JON

PS I have actually been programming asp.net for a while, and for internal
reasons my company has just taken a step backwards in technologies (don't
ask!). It's possible my questions above show that I am thinking in a .net
way, and that I will have to compromise my OOP principles when using asp 3.0
(though I hope not!).


**************
Article object
**************

function article(objR)
{
if (objR != null)
{
this.articleID = "" + objR("art_id");
this.title = "" + objR("art_titulo");
this.authors = GetAuthors(this.articleID);
}
}


function GetArticles(howMany...)
{
//prepare return Array
var result = new Array()

sql = "SELECT ................ "


objArticlesRS = Server.CreateObject("ADODB.Recordset")
objArticlesRS.Open(sql, objC)

while(!objArticlesRS.EOF)
{
result.push(new article(objArticlesRS));
objArticlesRS.MoveNext();
}

objArticlesRS.Close()
return result
}


**************
Author object
**************
function author(objR)
{
if (objR != null)
{
this.authorID = "" + objR("aut_id");
this.name = "" + objR("Aut_Nombre");
this.nickname = "" + objR("aut_nick");
this.email = "" + objR("aut_mail");
}
}



function GetAuthors(articleID)
{

//prepare return Array
var result = new Array()

sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID

objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
objAuthorsRS.Open(sql, objC)

while(!objAuthorsRS.EOF)
{
result.push(new author(objAuthorsRS));
objAuthorsRS.MoveNext();
}

objAuthorsRS.Close()
return result

}

[CJM]

"Jon Maz" <jonmaz@surfeuNOSPAM.de> wrote in message
news:uPFw2ASXEHA.3664@TK2MSFTNGP12.phx.gbl...

> Hi All,

[snip]

> The code I have written (below) works, but is very inefficient; if I want
> the asp page to fetch a list of 30 authors using GetAuthors(30), there

will

> be a total of *31* calls to the database - one call in GetArticles(), and
> one call to GetAuthors() for EACH article!
>
> This must be a common situation, and I'd be grateful for any strategies

for

> dealing with this. Any help much appreciated!
>

I'm not au fait with Javascript (is there a reason for this server-side
JS?), so forgive me if my interpretation is wrong, but I didnt think you
were making 31 calls to the DB. To achieve what you want, you need to one
call to return a single Article, and one call to return 30 Author records -
which is what I thought you seem to be aiming for.

Interestingly, your use of 'Select *...' well add extra round-trips, since
ADO must first determine which fields are available. It is always better to
explicitly specify the fields to be returned: 'Select A.Firstname, A.Surname
From Authors as A'

[snip]

> PS I have actually been programming asp.net for a while, and for internal
> reasons my company has just taken a step backwards in technologies (don't
> ask!). It's possible my questions above show that I am thinking in a .net
> way, and that I will have to compromise my OOP principles when using asp

3.0

> (though I hope not!).
>
>

You dont have to compromise your OOP principles. I'm not sure about JScript,
but you can use classes in VBScript if you so wish. This doesnt hold any
real benefits over standard procedural code.

cheers

Chris

PS. ASP developers might resent being part of a 'backward step'! :)

[Patrice]

You could read all authors for which you previously read articles (by using
a single select in the authors table with a where clause that includes all
the keys that were previously returned for the articles recordset and filter
this when the authors for a particular article are asked).

This is always a tradeoff anyway with exposing SQL data as objects (SQL
Server 2005 should provides enhanced support for this)...

Patrice

--

"Jon Maz" <jonmaz@surfeuNOSPAM.de> a écrit dans le message de
news:uPFw2ASXEHA.3664@TK2MSFTNGP12.phx.gbl...

> Hi All,
>
> I need some help optimising my object design. Since one article has 1+
> authors, I assume the logical way to represent this is for my Article

object

> to have a Authors property, of type "array", capable of representing any
> number of authors.
>
> The code I have written (below) works, but is very inefficient; if I want
> the asp page to fetch a list of 30 authors using GetAuthors(30), there

will

> be a total of *31* calls to the database - one call in GetArticles(), and
> one call to GetAuthors() for EACH article!
>
> This must be a common situation, and I'd be grateful for any strategies

for

> dealing with this. Any help much appreciated!
>
> TIA,
>
> JON
>
> PS I have actually been programming asp.net for a while, and for internal
> reasons my company has just taken a step backwards in technologies (don't
> ask!). It's possible my questions above show that I am thinking in a .net
> way, and that I will have to compromise my OOP principles when using asp

3.0

> (though I hope not!).
>
>
> **************
> Article object
> **************
>
> function article(objR)
> {
> if (objR != null)
> {
> this.articleID = "" + objR("art_id");
> this.title = "" + objR("art_titulo");
> this.authors = GetAuthors(this.articleID);
> }
> }
>
>
> function GetArticles(howMany...)
> {
> //prepare return Array
> var result = new Array()
>
> sql = "SELECT ................ "
>
>
> objArticlesRS = Server.CreateObject("ADODB.Recordset")
> objArticlesRS.Open(sql, objC)
>
> while(!objArticlesRS.EOF)
> {
> result.push(new article(objArticlesRS));
> objArticlesRS.MoveNext();
> }
>
> objArticlesRS.Close()
> return result
> }
>
>
> **************
> Author object
> **************
> function author(objR)
> {
> if (objR != null)
> {
> this.authorID = "" + objR("aut_id");
> this.name = "" + objR("Aut_Nombre");
> this.nickname = "" + objR("aut_nick");
> this.email = "" + objR("aut_mail");
> }
> }
>
>
>
> function GetAuthors(articleID)
> {
>
> //prepare return Array
> var result = new Array()
>
> sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID
>
> objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
> objAuthorsRS.Open(sql, objC)
>
> while(!objAuthorsRS.EOF)
> {
> result.push(new author(objAuthorsRS));
> objAuthorsRS.MoveNext();
> }
>
> objAuthorsRS.Close()
> return result
>
> }
>
>
>
>

[Mark Schupp]

Did you mean you want to get 30 "Articles" (you say "authors")?

If that is the case then the individual database statements for author
retrieval are the price of the data abstraction you get from your objects.

You might consider a new "articleList" object in which you get all of the
data for all articles at once by joining the articles and authors tables.
Then add a method "articleList.getNextArticle()" to retrieve the articles
one at a time. You will need a different article constructor that allows the
authors to be passed in as an array to do this.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Jon Maz" <jonmaz@surfeuNOSPAM.de> wrote in message
news:uPFw2ASXEHA.3664@TK2MSFTNGP12.phx.gbl...

> Hi All,
>
> I need some help optimising my object design. Since one article has 1+
> authors, I assume the logical way to represent this is for my Article

object

> to have a Authors property, of type "array", capable of representing any
> number of authors.
>
> The code I have written (below) works, but is very inefficient; if I want
> the asp page to fetch a list of 30 authors using GetAuthors(30), there

will

> be a total of *31* calls to the database - one call in GetArticles(), and
> one call to GetAuthors() for EACH article!
>
> This must be a common situation, and I'd be grateful for any strategies

for

> dealing with this. Any help much appreciated!
>
> TIA,
>
> JON
>
> PS I have actually been programming asp.net for a while, and for internal
> reasons my company has just taken a step backwards in technologies (don't
> ask!). It's possible my questions above show that I am thinking in a .net
> way, and that I will have to compromise my OOP principles when using asp

3.0

> (though I hope not!).
>
>
> **************
> Article object
> **************
>
> function article(objR)
> {
> if (objR != null)
> {
> this.articleID = "" + objR("art_id");
> this.title = "" + objR("art_titulo");
> this.authors = GetAuthors(this.articleID);
> }
> }
>
>
> function GetArticles(howMany...)
> {
> //prepare return Array
> var result = new Array()
>
> sql = "SELECT ................ "
>
>
> objArticlesRS = Server.CreateObject("ADODB.Recordset")
> objArticlesRS.Open(sql, objC)
>
> while(!objArticlesRS.EOF)
> {
> result.push(new article(objArticlesRS));
> objArticlesRS.MoveNext();
> }
>
> objArticlesRS.Close()
> return result
> }
>
>
> **************
> Author object
> **************
> function author(objR)
> {
> if (objR != null)
> {
> this.authorID = "" + objR("aut_id");
> this.name = "" + objR("Aut_Nombre");
> this.nickname = "" + objR("aut_nick");
> this.email = "" + objR("aut_mail");
> }
> }
>
>
>
> function GetAuthors(articleID)
> {
>
> //prepare return Array
> var result = new Array()
>
> sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID
>
> objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
> objAuthorsRS.Open(sql, objC)
>
> while(!objAuthorsRS.EOF)
> {
> result.push(new author(objAuthorsRS));
> objAuthorsRS.MoveNext();
> }
>
> objAuthorsRS.Close()
> return result
>
> }
>
>
>
>

[Jon Maz]

Hi Mark,

Beg pardon, you're quite right, I did mean to say "fetch a list of 30
articles using GetArticles(30)".

Let's see if I follow you: my current Article function is passed one row of
a recordset and creates one Article with it. You are suggesting crossing
the Article and Authors tables; the resulting recordset can contain >1 rows
pertaining to just one article object.

This means I would need a different Article constructor, your
articleList.getNextArticle() method, which would iterate through this more
complex recordset, processing out the necessary data from different rows to
create an Article object complete with Author info.

Is that right?

Thanks to all for the help so far,

JON

[Alan Howard]

Hi Jon,

You might also consider a method on your Article object - GetAuthors() -
which returns a recordset of Author records that you can just iterate
through. I use this technique quite often but it tends to be quick and
dirty - exposing a recordset to the higher layers is sometimes not ideal but
it means you can use GetRows, Sort, etc., directly if required. If you
wanted to strengthen the typing of your object model you could implement a
collection class called Authors on your Article object, and populate this
with individual Author objects. I haven't really looked into this myself
because I never seem to find the time. It is something I intend to look into
though.

Article (1) -> Authors (1) -> Author (0,m)

Interested to hear how you go.....

Alan

"Jon Maz" <jonmaz@surfeuNOSPAM.de> wrote in message
news:uPFw2ASXEHA.3664@TK2MSFTNGP12.phx.gbl...

> Hi All,
>
> I need some help optimising my object design. Since one article has 1+
> authors, I assume the logical way to represent this is for my Article

object

> to have a Authors property, of type "array", capable of representing any
> number of authors.
>
> The code I have written (below) works, but is very inefficient; if I want
> the asp page to fetch a list of 30 authors using GetAuthors(30), there

will

> be a total of *31* calls to the database - one call in GetArticles(), and
> one call to GetAuthors() for EACH article!
>
> This must be a common situation, and I'd be grateful for any strategies

for

> dealing with this. Any help much appreciated!
>
> TIA,
>
> JON
>
> PS I have actually been programming asp.net for a while, and for internal
> reasons my company has just taken a step backwards in technologies (don't
> ask!). It's possible my questions above show that I am thinking in a .net
> way, and that I will have to compromise my OOP principles when using asp

3.0

> (though I hope not!).
>
>
> **************
> Article object
> **************
>
> function article(objR)
> {
> if (objR != null)
> {
> this.articleID = "" + objR("art_id");
> this.title = "" + objR("art_titulo");
> this.authors = GetAuthors(this.articleID);
> }
> }
>
>
> function GetArticles(howMany...)
> {
> //prepare return Array
> var result = new Array()
>
> sql = "SELECT ................ "
>
>
> objArticlesRS = Server.CreateObject("ADODB.Recordset")
> objArticlesRS.Open(sql, objC)
>
> while(!objArticlesRS.EOF)
> {
> result.push(new article(objArticlesRS));
> objArticlesRS.MoveNext();
> }
>
> objArticlesRS.Close()
> return result
> }
>
>
> **************
> Author object
> **************
> function author(objR)
> {
> if (objR != null)
> {
> this.authorID = "" + objR("aut_id");
> this.name = "" + objR("Aut_Nombre");
> this.nickname = "" + objR("aut_nick");
> this.email = "" + objR("aut_mail");
> }
> }
>
>
>
> function GetAuthors(articleID)
> {
>
> //prepare return Array
> var result = new Array()
>
> sql = "SELECT * FROM Authors ..... WHERE a.art_id = " + articleID
>
> objAuthorsRS = Server.CreateObject("ADODB.RecordSet")
> objAuthorsRS.Open(sql, objC)
>
> while(!objAuthorsRS.EOF)
> {
> result.push(new author(objAuthorsRS));
> objAuthorsRS.MoveNext();
> }
>
> objAuthorsRS.Close()
> return result
>
> }
>
>
>
>

[Mark Schupp]

Yes, Just be sure to either disconnect the recordset or dump the raw data
into an array so that you can close the recordset and connection as soon as
possible.

To be completely honest about it however, I would probably stay with your
current approach unless it was demonstrably too inefficient. The additional
complexity could easily bite you in the behind.

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Jon Maz" <jonmaz@surfeuNOSPAM.de> wrote in message
news:emEJtySXEHA.2852@TK2MSFTNGP12.phx.gbl...

> Hi Mark,
>
> Beg pardon, you're quite right, I did mean to say "fetch a list of 30
> articles using GetArticles(30)".
>
> Let's see if I follow you: my current Article function is passed one row

of

> a recordset and creates one Article with it. You are suggesting crossing
> the Article and Authors tables; the resulting recordset can contain >1

rows

> pertaining to just one article object.
>
> This means I would need a different Article constructor, your
> articleList.getNextArticle() method, which would iterate through this more
> complex recordset, processing out the necessary data from different rows

to

> create an Article object complete with Author info.
>
> Is that right?
>
> Thanks to all for the help so far,
>
> JON
>
>

[Jon Maz]

Hi All,

In case anyone's interested, here's my solution.

Thanks to all for the help!

JON

---------------------------------------------------

function GetArticles(howMany...)
{
var basicArticlesArray = new Array()

var createdArticleIDs = "";

sql = "SELECT ................ "

objArticlesRS = Server.CreateObject("ADODB.Recordset")
objArticlesRS.Open(sql, objC)

while(!objArticlesRS.EOF)
{
basicArticlesArray.push(new article(objArticlesRS));
createdArticleIDs += objArticlesRS("art_id") + ",";
objArticlesRS.MoveNext();
}

objArticlesRS.Close()

var articlesWithAuthorsArray = AddAuthorsToArticles(basicArticlesArray,
createdArticleIDs);
return articlesWithAuthorsArray;
}





function AddAuthorsToArticles(articlesArray, articleIDString)
{
//turn the articleIDString into an articleIDClause
re = new RegExp(",", "gi");
var articleIDClause = "AND (art_id = " + articleIDString.replace(re, " OR
art_id = ") + ") ";

//create the Sql for querying the Authors table using this articleIDClause
sql = "SELECT * FROM Autor a ";
sql += "INNER JOIN Articulo_Autor aa ";
sql += "ON a.aut_ID = aa.aut_ID ";
sql += "WHERE a.aut_activo = 1 ";
sql += articleIDClause;

//get a recordset holding this Authors information
objlocalAuthorsRS = Server.CreateObject("ADODB.RecordSet");
objlocalAuthorsRS.Open(sql, objC);

//loop through the recordset containing authors information
while(!objlocalAuthorsRS.EOF)
{
//create an author object out of current objlocalAuthorsRS record
var newAuthor = new author(objlocalAuthorsRS);

//check the art_id field of objlocalAuthorsRS
var currentArticleID_Author = "" + objlocalAuthorsRS("art_id");

//get the Article with this articleID out of articlesArray
for (var i=0; i < articlesArray.length; i++)
{
var currentArticle = articlesArray[i];
var currentArticleID_Article = currentArticle.articleID;
var currentArticleIndex = GetIndex(articlesArray, currentArticle)

if (currentArticleID_Author == currentArticleID_Article)
{
//add the author object to the article.authors array
currentArticle.authors.push(newAuthor);

//put this ArticleWithAuthor object back into articlesArray, overwriting
the original ArticleWithoutAuthor
articlesArray[currentArticleIndex] = currentArticle;
}
}

objlocalAuthorsRS.MoveNext();
}
objlocalAuthorsRS.Close();
return articlesArray;
}


function GetIndex(array, object)
{
var index = dummyInteger;

for (var i=0; i < array.length; i++)
{
currentObject = array[i];
if(currentObject == object)
{
return i;
}
}

return index;
}




----- Original Message -----
From: "Alan Howard" <Xalan.howardX@Xparadise.net.nzX>
Newsgroups: microsoft.public.inetserver.asp.general,
microsoft.public.scripting.vbscript,
microsoft.public.scripting.jscript,microsoft.publi c.inetserver.asp.db
Sent: Tuesday, June 29, 2004 12:04 AM
Subject: Re: One-to-many relationship in asp 3.0 object design

Hi Jon,

Interested to hear how you go.....

Alan