Re: model inherited object

From: <j.andersen.lv_at_gmail.com>
Date: 28 Jun 2006 02:04:33 -0700
Message-ID: <1151485473.826792.119240_at_i40g2000cwc.googlegroups.com>

deja_at_2bytes.co.uk wrote:
> I have a table which I have previously used for objects of a certain
> class i.e.
>
> book
>
> this table has a number of columns representing the properties of the
> book i.e number of pages, author etc.
>
> I want to be able to record an inherited object so for example I will
> have Book1_v1 and Book1_v2 - the only difference between them being the
> number of pages and the title. However Book1_v3 might be inherited from
> Book1_v2 but with a different title only while Book1_v4 might be the
> same as Book1_v3 but with a different publication date.
>
> If I copy the Book1_v1 to a new record for Book1_v2 and change the
> number of pages, if I then change the author of Book1_v1, it will not
> be reflected in Book1_v2 (and it would be difficult to do via triggers
> because you cannot be certain which fields are "overridden").
>
> Even if I have a table that says [BookId, version number, property,
> value] to override any number of properties then I am still going to
> end up with horrible joins and datatype issues and working my way back
> from v4 to v1 to get a complete row would be a nightmare.
>
> What is the solution?

Hi Phil,

My suggestion is to use three tables! One for the objects, one for the attributes and one for the classifier defining the objects and attributes,
as well as domain values, ie. OBJECTLIST, ATTRIBUTELIST, DOMAIN1LIST, DOMAIN2LIST, etc.

In the classifier table you keep the list of objects and the list of attributes as:
ID
LABEL
DESCRIPTION
CLASSIFIER_FK <- reference to parent
(OBJECTLIST/ATTRIBUTELIST/DOMAINxLIST)
classifier.

In the objects table you keep all your objects, including the knowledge of
which object owns which other object, as: ID
OBJECT_TYPE_FK <- reference to CLASSIFIER.ID as a value from the OBJECTLIST.
OBJECT_FK <- reference to OBJECTS.ID as the owning object.

In the attributes table you keep all your object attributes, as: ID
VALUE <- manually entered value
VALUE_FK <- reference to CLASSIFIER.ID as a value from a DOMAINxLIST. ATTRIBUTE_FK <- reference to CLASSIFIER.ID as a value from a ATTRIBUTELIST.
OBJECT_FK <- reference to OBJECTS.ID as owning object. VALID_FROM <- date from which this value is valid! VALID_UNTIL <- date until which this value is valid! Default 2999.12.31 so
you don't have to work with NULL.

Retrieving an object and it's attributes becomes SELECT
   als.label, <- name of the attribute
   NVL(als.value,avl.label) <- if als.value is null, use avl.label FROM
   objects obj,

   attributes att,
   classifier ols,
   classifier als,

   classifier avl
WHERE obj.id = <your object id>

   AND <your date> BETWEEN att.valid_from AND att.valid_until

   AND obj.object_type_fk = ols.id
   AND att.object_fk = obj.id
   AND att.attribute_fk = als.id
   AND att.value_fk(+) = avl.id

Hope you get my meaning for the rest is up to you :)

Best wishes,

   John, Latvia Received on Wed Jun 28 2006 - 11:04:33 CEST