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
> >
>
