RE: EDS Database indexing?
> -----Original Message-----
> From: Euman [mailto:euman at bellsouth.net]
> Thanks Bernie and Matt,'
>
> Matt, the key is what I refer to as the index
>
> suppose I have a key #100 that has data that says it's a parent of
> key #105 and the data in #105 has data that says #100 is the
> parent and #115 is a child of #105 meaning key #100 is the
> grandparent.....(like a family tree, best example I could
> come up with.)
>
> if the user decided to delete #105 for instance, #100 when
> asked for the
> child record would return null
> instead of the sequence of data for key #106 since #105 is no
> longer there.
Again, I think you can simply delete the record, but you have to keep in
mind the difference between the record number (which is used to actually
fetch the record) and the record key (which is used to find the record
number).
Suppose we deleted the record with the key = 105, and the record with key
100 still points to 105 as it's 'child'. Now we pull 100 for some reason,
and want to also retrieve its children. The record's data tells us that 105
is a child, and here's what I'd recommend:
-- assume we've opened the database and selected our table
atom child, recnum, key
object data
key = 100
recnum = db_find_key( key )
if recnum < 0 then
-- no record!
return -- or whatever's appropriate
end if
data = db_record_data( recnum )
child = data[1] -- or however that piece of data is stored
-- now child holds the key value for the child
recnum = db_find_key( child )
if recnum < 0 then
-- no child!
return -- or whatever's appropriate
end if
data = db_record_data( recnum )
Obviously, I'm not checking for no child, etc, but I think you get the idea.
Basically, if the child has been deleted (or never existed), you'll get a
negative record number, and can disregard it. This might also be a good
time to clean your record to remove the reference to the non-existent child
record.
But should we delete the references to the deleted record? This is what's
known as referential integrity, and the answer is that we really should,
although I suppose you might have reasons for not, it's generally considered
good form to maintain integrity. In your example above, this would mean
that when you delete #105, you should alter the data in #100 to no longer
point to #105 (assuming that it did in the first place).
Of course, now the question is, how would you know that 100 points to 105?
Maybe you also record the inverse relationship in the child (ie, point to
the parent). This would make traversing your tree of data easier, but would
increase the storage space, and might not make sense for your application.
You could scan all records looking for said reference, but this wouldn't be
a good solution, unless you didn't have much data to look at.
Another option would be to maintain a separate index in another table. By
index, I'm really talking about something like an associative list. You
could store all indices on tables in one table, or create tables for each
one. Let's look at creating a separate table for each, since this puts more
work onto the shoulders of EDS.
Create a table named PARENT, and one named CHILD. PARENT will be a table
used to track the parents of records, and CHILD will track the children.
So, for your example, you might have these entries in PARENT:
105: {100}
115: {105}
and in CHILD:
100: {105}
105: {115}
We could simply add elements to the sequences that make the record data for
multiple children or parents. Now, if we wanted to delete 105, we'd be able
to tell pretty easily that it is a child of 100, and has 115 as a child. In
fact, we wouldn't need to store this data in our main table at all, but we'd
have to be careful that we updated our index tables PARENT and CHILD each
time we changed something. We also would probably want to create rules
about what could be deleted, or what to do about deleted records. So if
we've deleted 105, do we disallow the action because it has a child? If we
allow it, do we make 100 the parent of 115, or do we make 115 have no
parent?
Currently, I've got a system devised to do similar things in EuSQL, although
all indices are stored as records a single table, using Jiri's a-list code
to manipulate things. I've run into a stumbling block in making things
efficient in running queries (ie, how to combine the indices with
conditional clauses and joins among tables).
Hope this helps,
Matt Lewis
|
Not Categorized, Please Help
|
|
