My database, the master table (Customer) have over 5000 records and the
details table (Customer Orders)have over 20000 records.
cusDB:
CusID
Name
cusOrderDB:
CusID
OrderNo
OrderDate
There is one-to-many relationship between the two tables using CusID as the
key. At the moment, the application is for viewing data only.
Here is a stratight-forward and modified methods to show the relationship.
(1)
First, the straight-forward and stupid way is to scanning the whole
cusOrderDB for the CusID selected in the list of master table. However, it
took over 15 sec. (tested on my Zire71)
to fetch and display all the relevant records from the customer order table.
For small amount of data, that's ok but it is not acceptable to handle
larget amount of data.
(2)
In order to get better performance, I thought a way but too complicated on
conduit side.
I add a field to each record of cusDB and cusOrderDB:
cusDB:
CusID
Name
DetailRecNo
cusOrderDB
CusID
OrderNo
OrderDate
NextRecNo
The "DetailRecNo" and "NextRecNo" are the record numbers (cursors on the
Palm) of each record in cusDB and cusOrderDB in natural order.
The "DetailRecNo" of an master record is the record number of first child
record in cusOrderDB, and that cusOrderDB record's "NextRecNo" is next
sibling record in cusOrderDB. When there is no more sibling record and the
"NextRecNo" is set to -1. All this is done on conduit side during Sync.
e.g.
cusDB:
0 C0001 Peter 3
1 C0002 John 4
. . .
where the first column is the record no. (i.e. cursor).
cusOrderDB:
0 C0001 K0001 1/1/2003 -1
1 C0001 K0002 2/1/2003 0
2 C0001 K0003 2/1/2003 1
3 C0001 K0004 2/1/2003 2
4 C0002 K0010 2/2/2003 -1
. . .
With this method, the program on Palm can give the details records at once
by following the "DetailRecNo" and "NextRecNo" without any delay whichever
master record is selected. I'm not sure if it is a good way to handle
master-details relationship like that.
Also, I'm thinking whether it is possible to handle editable records in this
way.
I'm database programmer on desktop platform actually but I need to extend
some application on PDA platform in order to handle the growing demand of
viewing data on road.
Welcome to any advice and suggestion for database handling on the Palm.
--
For information on using the Palm Developer Forums, or to unsubscribe, please see
http://www.palmos.com/dev/support/forums/
