Hi Bax,
Your analysis was succinct and compelling. Undoubtedly the EJB
2.0 specification was designed with the relationship table (e.g.
OrderItem) in mind. You are also correct that the primary key of the
line item should not relate to a particular ordering need of a query--
although IMHO a sequential ordering within the "namespace" of a
particular order is sometimes a convenient way to generate a
synthetic key, regardless of its "order by" utility.
Of course, I'm thinking here about a persistence manager, rather
than an application. Sometimes the persistence manager will get
to generate the schema on deployment, and sometimes we can
direct the application developer in how to develop the schema to
work best with the persistence manager.
Often, however, the persistence manager will be asked to work with
an existing database schema. I think that the situation I described
will exist in practice--where the relationship information would be
part of the key of the dependent object. I would almost believe it is
a common case--do you agree? I'm not sure how to handle it in
conformance to the specification.
I appreciate your input. It's good to have a database guy hanging
out on this list. (I am not one!)
-Dan
On 2 Nov 00, at 20:48, Holger Baxmann wrote:
> hi all,
>
> as a hot burned (from already existing db's) rdbms man, i would mention the
> following:
> - a db schema has nothing to do with such things like line_numbers, because
> this depends on the use case of the db -> a db schema SHOULD be use case
> independend (for SHOULD look RFC's ;-)
> - in the relations we not the have the one<->many, rather we have
> 0:1<->0:many or 1:1<->1:many .....
> there are really many 0:0<->0:0 relations ;-)
> what here is mentioned is the difference between this lower bound 0 or 1.
> and there are many db schemas they lack this feature. think of a archival of
> orders, independend from the ordered items, tho they are related by
> invoices.
> so most of this i'll have the 3rd NF:
>
> Item:
> item_number (primary key)
> product
> quantity
>
> and Order:
>
> order_number (primary key)
> order_date
>
> and OrderItem:
>
> order_number (part of PK)
> item_number (part of PK)
>
> and the relations:
> Item(0:m)<->(1:1)OrderItem(0:1)<->(0:1)Order
> with orphaned (not nulled) OrderItems on the Order side to retrieve the
> orders from the archive.
>
> BTW: these line_item_no thing could resolved by using a maintainance field:
> Ins_Date filled by a trigger with the insert timestamp (i'm using in every
> table, if it is possible and the "SELECT * FROM" are not so many, a
> UPD_DATE, UPD_CNT and INS_DATE field for maintainance). it is only _one_
> kind of order by clause for the items.
>
> bax
>
>
> > Dan,
> >
> > I see what you mean, so why not this way:
> >
> > order_number (a foreign key)
> > line_item_number (not null, the pk)
> > product
> > quantity
> >
> > Only the line_item_number is the primary key. order_number is just a
> > foreign key and can be null.
> > This requires of course that the line_item_numbers are all unique and
> > not start to count from 1 for each order, but thats the way it is done
> > in your szenario - right? :-(
> >
> > Hmmm...
> >
> > \Daniel
> >
> >
> > Dan OConnor wrote:
> > >
> > > Hi Daniel,
> > >
> > > Your two cents are always welcome. Your solution does allow for a
> > > disconnected object as per the specification. However, a deployer
> > > should be able to map an entity using the EJB 2.0 CMP model
> > > without changing an existing normalized schema... especially if it
> > > would mean changing existing data. It would be inappropriate to
> > > require an organization to change existing line-item numbers (that
> > > might be used for various business purposes) just to take
> > > advantage of EJB technology.
> > >
> > > I believe the schema I described is quite common in practice.
> > >
> > > -Dan
> > >
> >
>
>
