Use of 'Line numbers' for certain tables

SQL Apprentice Question
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



Celko Answers

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

SQL Apprentice Question
Joe

I appreciate what you are saying, I do have an Invoice header table, that
contains among other things Invoice number,customer foreign key, purchase/
insertion order total price taxes etc.


Since the business is web printing and newspaper advertisments no inventory
codes are involved.


Since in order to display (print) information on an invoice at any time in
the future, data has to be read from a table.
How would you design a table so you could print the following on an invoice
after the proper header information is printed


This ad appeared in the May 22 2006 edition of the Yellowknifer
3 columns x 100 agates @ 1.00 Prices Slashed 300.00
Color charge Red 50.00
50.00
Goods & Service Tax
24.50
Invoice Total
374.50


Remembering that the same invoice form is used to print credit ,debit
memos, webprinting and overdue interest invoices


Celko Answers

>> Since the business is web printing and newspaper advertisments no inventory codes are involved. <<


No inventory? No ink, no paper is consumed? I assume that you mean
your job it out and provide only services as a middle man (I did that
in college, along with typesetting for high school and underground
newspapers)


>> Since in order to display (print) information on an invoice at any time in the future, data has to be read from a table. <<


No problem, but since when did any industry have an infinite record
retension schedule? Federal and State Archives are the only places I
have worked actually threw out nothing. That required microfilm in the
old days, and warehouses. Lots of warehouses.


>> How would you design a table so you could print the following on an invoice after the proper header information is printed


This ad appeared in the May 22 2006 edition of the Yellowknifer
3 columns x 100 agates @ 1.00
Prices Slashed 300.00
Color charge Red 50.00
Goods & Service Tax 24.50
Invoice Total 374.50 <<

It is a bit hard to design a full job costing system in a newsgroup.
(I also had to look up agate line = 1/14", which does not match to the
Point system or Metric). But each one of the services is a detail with
a description. I remember that DEC had a classified ad system
(TypeSet-11) with a pretty good set of codes in its day. Use them to
replace the SKU in a regular retail operation.


I tend to favor a hierarchical encoding for production work, but leave
the price of penalty copy open for a manual entry.



>> Remembering that the same invoice form is used to print credit, debit memos, webprinting and overdue interest invoices <<


How about using this magical universal form for passports and birth
certificates, too? Absurd! You have a forms control officier who is
not going to approve that kind of design. Hell, the legal boilerplate
for credit & debit memos has nothing whatsoever to do with invoicing,
and vice versa.

The same database might track all of these things, but you are once
again confusing the physical paperwork with the logical data.


You do about http://www.bfma.org/bookstore/main.php? On the display
side of the house, do the right job. On the database side of the
house, do the job right. These days, you are not pre-printing most
forms anyway (email and laser printing, not ink); you do not need a
warehouse to store them.