Dev - Data modeling principles

[cjhowe]

In this example then, would it be better (more
correct, not better as far as needing to refactor the
code) if the BillingAccount entity had a primary key
of paymentMethod instead of billingAccountId and
followed the style of CreidtCard and EftAccount? ...
or would it be better to have a join table?

====================David Jones wrote:

The fromDate as part of the PK is part of the
effective date pattern,  
and having the thruDate not be part of the PK is the
same way. The  
point is not to avoid duplication with a finely
grained time slot, in  
fact it should _NEVER_ be used to artificially
distinguish (ie by  
anything except time) one record from another. Never.

A general principle of data modeling is to use
composite primary keys  
whenever possible, especially for "join" tables. This
is an important  
part of the OFBiz data model. Only "primary" entities,
ie those that  
exist somewhat independently like Product,
OrderHeader, Party, etc  
should have sequenced IDs. Abusing those causes all
sorts of  
problems... and when you have hundreds of tables those
problems  
become extremely expensive to manage in a hurry.

-David
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

The BillingAccount structure is a pretty different one. A  
BillingAccount is not a PaymentMethod because it can be used outside  
that context and it really isn't a PaymentMethod, it is a way of  
tracking invoices and payments for billing purposes. In other words,  
it does _not_ constitute payment of an invoice or a way to pay an  
invoice, it is just a place holder to make it easier to track.

-David


On Feb 27, 2006, at 7:32 PM, Chris Howe wrote:

> In this example then, would it be better (more
> correct, not better as far as needing to refactor the
> code) if the BillingAccount entity had a primary key
> of paymentMethod instead of billingAccountId and
> followed the style of CreidtCard and EftAccount? ...
> or would it be better to have a join table?
>
> ====================David Jones wrote:
>
> The fromDate as part of the PK is part of the
> effective date pattern,
> and having the thruDate not be part of the PK is the
> same way. The
> point is not to avoid duplication with a finely
> grained time slot, in
> fact it should _NEVER_ be used to artificially
> distinguish (ie by
> anything except time) one record from another. Never.
>
> A general principle of data modeling is to use
> composite primary keys
> whenever possible, especially for "join" tables. This
> is an important
> part of the OFBiz data model. Only "primary" entities,
> ie those that
> exist somewhat independently like Product,
> OrderHeader, Party, etc
> should have sequenced IDs. Abusing those causes all
> sorts of
> problems... and when you have hundreds of tables those
> problems
> become extremely expensive to manage in a hurry.
>
> -David
>
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[cjhowe]

Hmmm..It's rather difficult for me to wrap my head
around how to treat the placeholder

Would a better way to treat that placeholder be to
utilize an internal_credit_card as payment option
instead of utilizing the actual billingAccount?

Or is it more appropriate to continue creating
specialized code to handle billing accounts in
transactions?

--- "David E. Jones" <[hidden email]> wrote:

>
> The BillingAccount structure is a pretty different
> one. A  
> BillingAccount is not a PaymentMethod because it can
> be used outside  
> that context and it really isn't a PaymentMethod, it
> is a way of  
> tracking invoices and payments for billing purposes.
> In other words,  
> it does _not_ constitute payment of an invoice or a
> way to pay an  
> invoice, it is just a place holder to make it easier
> to track.
>
> -David
>
>
> On Feb 27, 2006, at 7:32 PM, Chris Howe wrote:
>
> > In this example then, would it be better (more
> > correct, not better as far as needing to refactor
> the
> > code) if the BillingAccount entity had a primary
> key
> > of paymentMethod instead of billingAccountId and
> > followed the style of CreidtCard and EftAccount?
> ...
> > or would it be better to have a join table?
> >
> > ====================David Jones wrote:
> >
> > The fromDate as part of the PK is part of the
> > effective date pattern,
> > and having the thruDate not be part of the PK is
> the
> > same way. The
> > point is not to avoid duplication with a finely
> > grained time slot, in
> > fact it should _NEVER_ be used to artificially
> > distinguish (ie by
> > anything except time) one record from another.
> Never.
> >
> > A general principle of data modeling is to use
> > composite primary keys
> > whenever possible, especially for "join" tables.
> This
> > is an important
> > part of the OFBiz data model. Only "primary"
> entities,
> > ie those that
> > exist somewhat independently like Product,
> > OrderHeader, Party, etc
> > should have sequenced IDs. Abusing those causes
> all
> > sorts of
> > problems... and when you have hundreds of tables
> those
> > problems
> > become extremely expensive to manage in a hurry.
> >
> > -David
> >
> > _______________________________________________
> > Dev mailing list
> > [hidden email]
> > http://lists.ofbiz.org/mailman/listinfo/dev
>
> >  
> _______________________________________________
> Dev mailing list
> [hidden email]
> http://lists.ofbiz.org/mailman/listinfo/dev

 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[davidnwelton]

On 2/28/06, Chris Howe <[hidden email]> wrote:

> ====================David Jones wrote:
>
> The fromDate as part of the PK is part of the
> effective date pattern,
> and having the thruDate not be part of the PK is the
> same way. The
> point is not to avoid duplication with a finely
> grained time slot, in
> fact it should _NEVER_ be used to artificially
> distinguish (ie by
> anything except time) one record from another. Never.

> A general principle of data modeling is to use
> composite primary keys
> whenever possible, especially for "join" tables. This
> is an important
> part of the OFBiz data model. Only "primary" entities,
> ie those that
> exist somewhat independently like Product,
> OrderHeader, Party, etc
> should have sequenced IDs. Abusing those causes all
> sorts of
> problems... and when you have hundreds of tables those
> problems
> become extremely expensive to manage in a hurry.

Is this covered in the data model book?  I'd be curious to read more
about this - it strikes me as something that isn't obvious, and I'd
like to learn more about the reasoning and experience behind it.

Thanks,
--
David N. Welton
 - http://www.dedasys.com/davidw/

Linux, Open Source Consulting
 - http://www.dedasys.com/
 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev

[David E. Jones]

On Feb 28, 2006, at 3:41 AM, David Welton wrote:

>> ====================David Jones wrote:
>>
>> The fromDate as part of the PK is part of the
>> effective date pattern,
>> and having the thruDate not be part of the PK is the
>> same way. The
>> point is not to avoid duplication with a finely
>> grained time slot, in
>> fact it should _NEVER_ be used to artificially
>> distinguish (ie by
>> anything except time) one record from another. Never.
>
>> A general principle of data modeling is to use
>> composite primary keys
>> whenever possible, especially for "join" tables. This
>> is an important
>> part of the OFBiz data model. Only "primary" entities,
>> ie those that
>> exist somewhat independently like Product,
>> OrderHeader, Party, etc
>> should have sequenced IDs. Abusing those causes all
>> sorts of
>> problems... and when you have hundreds of tables those
>> problems
>> become extremely expensive to manage in a hurry.
>
> Is this covered in the data model book?  I'd be curious to read more
> about this - it strikes me as something that isn't obvious, and I'd
> like to learn more about the reasoning and experience behind it.

The Data Model Resources Book(s) have some data modeling principles  
in them, but there are other books more dedicated to that subject  
that would be a better source of such things. Looking at my bookshelf  
I don't see any that I've kept around, but there are certainly lots  
of good ones. One guy I met a little while back at a new IT oriented  
school in Salt Lake City that has some good books is Joe Celko. His  
books are mostly more advanced and very heavy on direct SQL  
programming (the Entity Engine is a bit of an over-simplified  
abstraction in some cases from this perspective), but he has lots of  
good ideas about ways of achieving more natural (ie real-world, etc)  
modeling and good practices.

-David


 
_______________________________________________
Dev mailing list
[hidden email]
http://lists.ofbiz.org/mailman/listinfo/dev