Re: layout question

On Sat, Jun 12, 2010 at 10:56:34PM +0200, Jean-Yves F. Barbier wrote:
> Hi list,
> 
> I must build a kind of ERP and I wonder what's the best DB layout,
> especially about quotations/orders/delivery orders/invoices/RMAs/credit
> notes.
> 
> It must be very scalable (say from 10 invoice a day to 2,000 or even more.)
> 
> As these docs' structures are very close, what is the best:
> to have one table per kind of doc and to copy info from one to another,
> or to have only one table with a flag telling the doc's type and external
> sequences to keep numbering coherent? (also means the docs' lines table
> will be pretty huge.)

Have you considered using one of the ERP packages already available? For
instance, one of those listed here:
http://en.wikipedia.org/wiki/List_of_ERP_software_packages

Your specific question can be generalized somewhat: given a requirement to
store records of several closely related types, is it best to store them all
in one table, or separate, per-type tables. You're likely to find several
different answers, depending on whom you're asking. A few key points to
remember: 

* Make sure your model allows you to create meaningful constraints
* Avoid the temptation to use PostgreSQL's inheritance mechanism for this
  purpose.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com