On 12/03/2004 21:26, "Avinash Gangadharan" <[EMAIL PROTECTED]> wrote:
> People,
> I have a design question. It is regarding the retrieval of parent and
> child objects in the best possible manner. For eg. Let's say there is a
> Person object with properties such as ssn, lastName and firstname. It also
> has a list child objects ( "Address" ) of addresses and a list of previous
> employees ( "Employee" ). The Person, Address and Employee objects are
> separate tables in the DB linked thru ssn.
> Now what do you guys think is the most efficient way of retrieving a
> complete Person.
If you're talking about SQL, can't you issue one or two SQL queries (with
subselects, inner or outer joins) or write a stored procedure to retrieve
all the info? (I am used to work with MySQL so my advanced SQL is a bit
rusty)
Continuing with simple SQL queries only... You could do this in a 2 step
mode:
- retrieve all persons who have addresses and employees (1 query per
person)
- for the remaining people (1-2 queries per person)
- get the addresses (select... where person.ssn = address.ssn and id <>
...) (1 query per person)
- get the employees (similar to the previous case) (1 query per person)
(anyway you get 3 full table search)
If you had to had 2 flags on the Person - hasAddresses and hasEmployees -
you could make the queries more simple - fetch for (1-1), (1-0) and (0-0) -
but maybe it would be more difficult to maintain.
If you are talking about a persistence layer (like OJB - see ojb-user
mailing list) some of them have lazy loading: it only loads the Person
collection of Addresses only if you use them.
This will certainly be simpler... And if you have object cache it could
even things up (maybe you could issue a great number SQL queries but if the
objects were already in cache then there would be no need for any SQL
query).
>
> The simplest way is ofcourse issue 3 queries one for person, other for the
> list of address and the third for previous employees. But this does not
> scale well with increasing size of the table data. For 100 rows of Person
> 201 queries are issued. 1 which gets 100 persons, 100 each for there
> subequent address and previous employees. Similarly for a 1000, 2001
> queries.
>
> Complex queries may be a solution which relies on filtering the resultset
> but is there a more simple and intelligent approach.
>
> Is there a problem with the inherent design itself or is this a common
> problem.
I donšt see any problem on the design itself (well maybe... it seems you
have the social security number has the primary key... pks are not supposed
to have any real meaning -> what would happen if all of the social security
numbers were to be changed? It would generate a huge number of cascade
updates. Do whatever you like maybe I am raising a false issue here).
Hope it helps,
Pedro Salgado
>
> Thanks and Regards
> Avinash
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
