Re: Constraining primary keys (was: [Modeling-users] primary key + class property)

[Sebastien B. <sbi...@us...>]

        Hi,

> > - you have some tables, say T1, T2 and T3, for which you want to ha=
ve a
> >   unique pk-generation scheme, i.e. where a pk value in T1 cannot b=
e a
> >   pk value in t2 and in t3
>
> Exactly.

Okay, let's consider they are all sub-entities of entity Node

> [...] i tend to think of these relations being always of type
> dataTable-to-indexTable. Thus, indexTable rows are really not aware of
> what they point to (or rather, who is pointing to them) -- except,
> obviously, that one would be able to navigate from an indexTable row
> to a dataTable row, even without explicit outgoing relationships on
> the indexTable, facilitated by the fact that the primaryKey are
> identical. However, from the dataTable end, each table provides an
> explicit relationship into any desired indexTable, helping enforce
> that the management of a data row's index entry "belongs" to the data
> row, i.e. the logic to add or delete a data row will include the logic
> to add or delete (or modify) all affected index entries.
>
> Example:
>
> NODE      an index table
> id:           int pk
> name:         str
> type: enum : A | B | C  (defined via some rel to another TYPE table,
>                          thus adding possibility to specify
>                          some meta-data for each type, e.g; an icon)
>
> TREE  index imposes a hierarchy on NODE rows
> treeId:       int pk
> parent        rel to Node.pk
> child         rel to Node.pk
>
> A
> id:           int pk
> a1
> a2
> toNode        explicit relation to Node, and with A.id =3D Node.id
>
> similarly for B and C
>
> How would be best to model such a structure?

  My first thought about this is that the underlying model might look l=
ike:

[1]    +-children----+
       |             |
       v             |
       v             |
    +------+         |
    | Node |<-parent-+
    +------+=20=20=20=20=20=20=20=20=20=20
       |
    +--+--+
    |  |  |
    A  B  C

which can be modeled just straightforward. However I suspect that your
situations requires to keep the hierarchy structure separated from the
informations stored in table A, B and C. In this case, I guess you may
consider this model:

[2]                             +--children---+
                                |             |
                                v             |
                                v             |
    +------+                 +------+         |
    | Node |<-node-----tree->| Tree |<-parent-+
    +------+                 +------+
       |
    +--+--+
    |  |  |
    A  B  C


Given that one-to-one is not supported yet, the node/tree relationship
must be modeled as a one-to-many rel. If you really do not want to have
*any* informations in tables A, B and C about the tree structure (that
is to say, that's a requirement at the relational level, nnot on the
objects' side), just design the toNode as to-one and toTree as to-many
(this way, the relationship is retrieved via a foreign key declared in
the table Tree)


  However and re-reading your post, the following things seem strange t=
o me:

> NODE
> type: enum : A | B | C  (defined via some rel to another TYPE table,
>                          thus adding possibility to specify
>                          some meta-data for each type, e.g; an icon)
[...]
> A:
> toNode        explicit relation to Node, and with A.id =3D Node.id

I wonder whether you're not too tight to the way you'll do this by hand
with raw SQL, or if I'm missing something. Let me comment on this:

   You say A.id=3DNode.id, but you'll never get this: the framework mak=
es
   sure that each row of Node (which will in fact be an empty table,
   since no elements of type Node would ever be instanciated), A, B or C
   will have different PK. In the case you think of it as a mean to
   actually retrieve the corresponding object pointed to in either A, B
   and C, you do not need it: the framework automatically takes care of
   this for you. E.g. in [1], parent & children relationships can point
   to objects of type A, B or C (same for toNode in [2]).

Same for the other entity Type: just model it the way the association
between Node & Tree is made in [2] (or alternatively put the common
meta-datas in entity Node and specific metadatas in entities A, B & C
--at least if the set of metadatas are not subject to changes after
valuable datas are stored within the database: if they are, the first
solution is a better one for sure).

> Ah, i did not realise that inheritance enforces that pk's are unique
> across all rows in the inheritance tree! In the above example, then,
> would it be enough to make A, B and C inherit from NODE?=20

I think so

> (This also raises the question whether the limitation mentioned in the
> manual in section 2.3.2 is still valid... namely that "that toMany
> relationships must have inverse (toOne) relationships defined in the
> model".)

Yes, indeed, this is still valid.

> I am looking for a way to implement the example construct above, while
> staying as light as possible (minimum number of explicit relations) a=
nd
> at the same time maximising automation (framework) of the management
> of the relations.

  I'm not clear at all on what you mean by: minimum number of explicit
  relations. Why do relations bother you so much? Is this because of the
  previous statement about toMany relations being required to have an
  inverse?  This requirement has really nothing to do w/ inheritance, it
  just says that a toMany relationship from entity e1 to e2 must have an
  inverse. Now speaking of inheritance, the sub-entities *must* include
  the set of the inherited attr. & rels., this is because the model maps
  object to relational --however your (generated) classes will _inherit_
  the relations and attributes, and they wont re-declare them: the
  replication will only be in the model. Similarly, the business-logic
  attached to rel. toTree in class Node will be inherited in A, B and C.

> Have not yet tried to prototype such an example, but will plunge soon=
...
> Any "wisdom" comments very much appreciated.

I just hope I succeeded to make myself clear, and that I did not miss
some important points in your post...

        Cheers,

-- S=E9bastien.