[thelist] recursive database design

[Joshua Olson]

From: "Dusty McDonald" <fndlm at uaf.edu>
Sent: Tuesday, December 23, 2003 4:19 PM


> I gather you think the current model is appropriate - ie, there is nothing
> wrong with pretending that objects are containers? That's always
> seemed a bit hinky to me, but I can't figure out why - hence this
question.

Dusty,

>From your structure, this last statement does not seem accurate.  It seems
that you have containers in one table and objects in another table related
together by the coll_obj_cont_hist.

In the abstract sense, there's nothing wrong with objects serving as
containers.  Moreover, it could be said that this abstraction is probably
more correct than disallowing objects from being containers.  But, from this
structure, I don't see how objects can be containers.  Clearly containers
can contain objects and other containers... but there seems to be no way for
object to actually become a container.

One thing you *could* do (and this is drastic) is to completely remove the
differentiation altogether.

One table would contain both containers AND objects, and another table
contains attribute pairs describing the containers and objects.

Example:

~~~~ Table1:
container_id
parent_container_id
common_name

~~~~ Table2:
container_id (f-key to Table1)
key
value
(key and container_id would be the PK of this table)

Table1 represents either objects or containers.  For each record in Table1
there may be many records in Table2 that provide additional information
about the object.  If you want extra differentiation between containers and
objects, then you could add a flag to Table1 that creates that
differentiation, though you could also determine this be looking to see if
the object contains other objects... if it does, then it's a container.

Please note that the above structure provides only the properties of the
objects and the relationship between objects, but does not provide an audit
of the objects, which was one of your requirements and thusly something
you'd need to track in another table.

There are quite a few other "cool" things that can be done here, but most of
them make the structure even more unruly.

This conversation is probably not of interest to some people on the list as
it's somewhat divergent from basic web technologies--but I'd love to discuss
it with you more.  Please contact me offlist if you have any additional
questions.

<tip type="SSL" author="Joshua Olson">
Some issuers of SSL will charge you a fee to reissue a certificate for a new
server in the event of a failure or server move.  There is typically no
reason to pay that fee.  Back up the certificate (include the private key),
and then restore it on the new machine.  There is nothing in the certificate
that binds it to a particular machine or IP address.

On Windows, you can access the certificate store by opening up MMC and
running the "Certificates" snap-in for the "Computer account" on the "Local
Computer".  Locate the certificate under "Personal" and then right-click,
All Tasks, Export...

Make sure you select that the private key should be exported, and include
all the certificates in the certification path.  Do not selected to have the
private key deleted if you are not actually ready to stop using the existing
certificate.

On the new box simply reverse the process.
</tip>

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/AL/
706.210.0168