> Hello
> Been reading posts by Celko et al about not using 'line number' column in a
> table. This I can understand except in the case of a 'Invoice detail' table
> where the lines must appear in a certain sequence on the customers invoice.
> IE certain lines are calculated after user input - "Taxable amount", "Goods
> and Service Tax" etc. The invoice looks better if these are listed last
> If 'Line numbers" should not be used what is the alternative?
>
> My table is as follows
>
> CREATE TABLE [dbo].[ArInvDetail] (
>  [InvoiceNo] int DEFAULT(1) NOT NULL,
>  [Line] smallint DEFAULT(1) NOT NULL,
>  [Descript] char(70) NULL,
>  [Amount] decimal(12, 2) DEFAULT(0) NOT NULL,
>  [UnitPrice] decimal(12, 2) DEFAULT(0) NOT NULL,
>  [Quantity] decimal(5, 0) DEFAULT(0) NOT NULL,
>  [Transtype] char(1) DEFAULT('A') NOT NULL,
>  [Reference] int DEFAULT(1) NOT NULL
> )
> GO
>
> ALTER TABLE [dbo].[ArInvDetail] ADD CONSTRAINT [ArInvDetail] PRIMARY KEY
> CLUSTERED  [InvoiceNo] ASC, [Line] ASC)
> GO
>
>
> --
> Life is a journey, wear comfortable shoes
> Tom Moreau Ph,D
>
>
>
>
> John Linville

> Hello
> Been reading posts by Celko et al about not using 'line number' column in
> a table. This I can understand except in the case of a 'Invoice detail'
> table where the lines must appear in a certain sequence on the customers
> invoice. IE certain lines are calculated after user input - "Taxable
> amount", "Goods and Service Tax" etc. The invoice looks better if these
> are listed last
> If 'Line numbers" should not be used what is the alternative?
>
> My table is as follows
>
> CREATE TABLE [dbo].[ArInvDetail] (
> [InvoiceNo] int DEFAULT(1) NOT NULL,
> [Line] smallint DEFAULT(1) NOT NULL,
> [Descript] char(70) NULL,
> [Amount] decimal(12, 2) DEFAULT(0) NOT NULL,
> [UnitPrice] decimal(12, 2) DEFAULT(0) NOT NULL,
> [Quantity] decimal(5, 0) DEFAULT(0) NOT NULL,
> [Transtype] char(1) DEFAULT('A') NOT NULL,
> [Reference] int DEFAULT(1) NOT NULL
> )
> GO
>
> ALTER TABLE [dbo].[ArInvDetail] ADD CONSTRAINT [ArInvDetail] PRIMARY KEY
> CLUSTERED  [InvoiceNo] ASC, [Line] ASC)
> GO
>
>
> --
> Life is a journey, wear comfortable shoes
> Tom Moreau Ph,D
>
>
>
>
> John Linville
>
>
>
>

> Hello
> Been reading posts by Celko et al about not using 'line number' column in
> a table. This I can understand except in the case of a 'Invoice detail'
> table where the lines must appear in a certain sequence on the customers
> invoice. IE certain lines are calculated after user input - "Taxable
> amount", "Goods and Service Tax" etc. The invoice looks better if these
> are listed last
> If 'Line numbers" should not be used what is the alternative?
>
> My table is as follows
>
> CREATE TABLE [dbo].[ArInvDetail] (
> [InvoiceNo] int DEFAULT(1) NOT NULL,
> [Line] smallint DEFAULT(1) NOT NULL,
> [Descript] char(70) NULL,
> [Amount] decimal(12, 2) DEFAULT(0) NOT NULL,
> [UnitPrice] decimal(12, 2) DEFAULT(0) NOT NULL,
> [Quantity] decimal(5, 0) DEFAULT(0) NOT NULL,
> [Transtype] char(1) DEFAULT('A') NOT NULL,
> [Reference] int DEFAULT(1) NOT NULL
> )
> GO
>
> ALTER TABLE [dbo].[ArInvDetail] ADD CONSTRAINT [ArInvDetail] PRIMARY KEY
> CLUSTERED  [InvoiceNo] ASC, [Line] ASC)
> GO
>
>
> --
> Life is a journey, wear comfortable shoes
> Tom Moreau Ph,D
>
>
>
>
> John Linville
>
>
>
>

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1151194333.453993.89870@i40g2000cwc.googlegroups.com...
>>> Been reading posts by Celko et al about not using 'line number' column
>>> in a table. This I can understand except in the case of a 'Invoice
>>> detail' table where the lines must appear in a certain sequence on the
>>> customers invoice. <<
>
> That is a display issue and has nothing to do with the data.  Did your
> customer buy a line number?  What you are doing is copying a PHYSICAL
> PAPER FORM into a table.  But tables are not files and they do not work
> that way.
>
> What you want to do with the invoicie details is make sure that each
> item appears one and only one (remember that mania RDBMS has about
> redundancy?  Normal forms?  attribute splitting? etc.?))  This means
> that you have a quantity column, price column, and some other stuff for
> other business rules.
>
> The duty of the front end is to prevent things like splitting an order
> item into two details lines with the same item on it, invalid SKU codes
> (check digits), invalid dates, etc.
>
>>>i.e. certain lines are calculated after user input - "Taxable amount",
>>>"Goods and Service Tax" etc. The invoice looks better if these are listed
>>>last If 'Line numbers" should not be used what is the alternative? <<
>
> "Looks better"  -- mmm, display issue that has nothing to do with the
> schema design.  Want to store logos, colors and other such things in
> the table?  Of course not!
>
> Your design has some errors in it.  Details imply a header and you do
> not have one!  The price per unit should be in the inventory (unless
> each item is priced individually)
>
> Why is the default quantity set to zero and not one?  What does an
> order that size mean?  Why did you make it a DECIMAL(5,0) and not an
> INTEGER?  I'll bet you are thinking about the display, like COBOL!
>
> Where is the SKU of the item you are selling?  The inventory should
> have the price, description, tax type and all the things that apply to
> each item.
>
> CREATE TABLE InvoiceDetails
> (invoice_nbr INTEGER DEFAULT(1) NOT NULL,
>   REFERENCES Invoices(invoice_nbr)
>   ON UPDATE CASCADE
>   ON DELETE CASCADE,
> sku CHAR(9) NOT NULL
>   REFERENCES Inventory(sku)
>   ON UPDATE CASCADE
>   ON DELETE CASCADE,
> order_qty INTEGER DEFAULT(1) NOT NULL
>  CHECK (order_qty > 0)
> );
>
> When I have some special discounts, I will put them into the Inventory
> with a SKU.  The quantity discounts can be handled with quantity ranges
> in Inventory.  Combination packages are relational divisions that carry
> a SKU.  But you need to look at your business rules.
>