Databases.... how on earth?

[mark smit]

Hello,


I was wondering if someone could please explain to me how databases
can handle so much data? I thought it would be easy to write one
and now i'm finding that maybe this was a bit over my head.

Here is what I have as constraints...

1. the database is ment to store well data
2. each well has about twenty tables
3. none of the records are the same size
4. for each well some of the tables can have upwards of 50000 records
5. data retrieval must be very quick
6. data adding/updating/deleting can be slower

If anybody could point me in the right direction I would be very greatfull!



------------------
Cheers

[Chris Boss]

Mark;

You may need to use "pointers" in your database to track
multiple records for each item.

You can use a "single" record in one file for each item and
in that record save the number of records currently stored
for that item (in another file) and a pointer to the first
record for that item (in another file).

Then in the second file you save the first record for an item
then save the record pointer (record number) in the first file.

Then when a new record is added, you go to the first record, via
the pointer in the first file. Then save the new record at the end
of the second file and in the first record for that item, save
a pointer to the second record. Every new record saved, should have
a pointer to it saved in the previous record for that item.

To read in all the records for an item, simply read file 1 and get
the record count for that item and a pointer to the first record
for that item in file 2. Then read record 1 in file 2. Get a pointer
from record 1 for record 2 and read it from file 2. Then get a
pointer for record 3 from record 2 and read it from file 2 and so on.


By each record having a pointer to the next record in the list for
a particualr item, you have have unlimited records.

Make sure that each record also includes a field to indicate
which item the record belongs to. This way if the pointers get
corrupted, you can read the entire file and rebuild all the pointers.



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

[Ian Cairns]

Mark,

I have done several of these types of databases in DOS. This is the
approach that I have used: Create 3 random access files, with the
following structures modified to suit your data requirements.

Random Access File 1: WellID.dbf
Record 0 will contain the number of records in this file
Use a Union to extract it from Record 0.

TYPE WellInfoTYPE
  deleted  AS STRING * 1 
  wellID   AS STRING * 20
  wellCode AS LONG
  noTables as long
END TYPE

Random Access File 2: WellInfo.dbf
Record 0 will contain the number of records

TYPE WellIDTYPE
  deleted   AS STRING * 1
  wellCode  AS LONG  ' Keeps repetitive data to a minimum
  tableID   AS STRING * 20
  tableCode AS LONG
  firstRec  AS LONG  ' Gives you a place to start. 0 = no records.
END TYPE

Random Access File 3: WellRecords.dbf
Record 0 will contain the number of records

TYPE TableRecordTYPE
  deleted    AS STRING * 1
  tableCode  AS LONG  ' This confirms that it is the record you want
  prevRecPTR AS LONG  ' If 0, this is the first record
  nextRecPTR AS LONG  ' If 0, this is the last record
  record     AS STRING * 20
END TYPE

If a record is deleted, then put a "*" or similar character in the
'deleted' field. Then just write over it with other data when more
space is needed. Use 'linked list' type of routines to organize
WellRecords.dbf as it is built and when it is modified.

Happy Programming,

------------------
[email protected]

[This message has been edited by Ian Cairns (edited September 29, 2000).]

[Paul Dwyer]

Not sure about PB but normally they are created with Objects. Like Criss says the system is based on pointers but often on object pointers.

So you'd have record set objects, row objects and feild objects. after all when a new db is created you have to specify the data structure at run time which you can't do with standard data types

It's a bit of a pain to implement a DB like that which is why people tend to use commercial DBs or just make a new flat file system for each program they write.

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

[mark smit]

Hi guys!

Thanks for the replys but there is another problem here. There
are two copies of the database, one is on the geologists laptop
and the other is here at work. The geologists need to send there
data to the server so it can package it up and send it out to the
well owners. The biggest problem with sending is the weak cell
phone link. Way out in the field cell coverage is the pits. If we
can get five minutes of air time we concider it a good day. To
combat the weak linkage we only want to send data that has
changed since the last time the geologist has sent...

I'm a bit worried about using link lists because if one link
becomes broken then all the data following that will be lost?




------------------
Cheers

[Ian Cairns]

Mark,
1. Working with linked lists, you have to keep enough data to rebuild
the lists from scratch. That is the purpose of the "wellCode" and
"tableCode" fields. these MUST be Unique ID's. This part of the program
MUST be robust and thoroughly tested and debugged with "worst case"
scenarios.
2. Keep backups! and keep backups of the backups!
3. Send only the changed record to the server. Send it twice if necessary
and only accept data when it has matched twice. Do a search on the Unique
ID's in the Master Database. Make changes as necessary. Send out only
the updated information to the well owner. He doesn't need anyone else's data?

Regards,

------------------
[email protected]

[mark smit]

Hello,

No other data is needed. The server will make a PDF file of the
strip log and make RTF files for what ever forms/reports are needed


Just out of curiosity, Where do you work Ian?

------------------
Cheers

[Ian Cairns]

Mark,
I have one of the best jobs in the world. I work at a Research Forest Nursery
(Overpaid, underworked, government civil servant - Province of BC, Canada) on a
prime fishing lake (Cowichan Lake) in pristine wilderness, surrounded with small
communities, and only 30 minutes from major shopping centres.
Situated on Vancouver Island, Isolated from the big cities. A different job to
do every day. Never a dull moment when growing trees. (eat your hearts out )
Regards,

------------------
[email protected]