Use of 'Line numbers' for certain tables

Use of 'Line numbers' for certain tables

Post by John Linvi » Sun, 25 Jun 2006 23:22:47


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
 
 
 

Use of 'Line numbers' for certain tables

Post by David Port » Sun, 25 Jun 2006 23:49:39


If the presentation order is significant then there's nothing wrong
with putting that information in a table. A line number denoting
position on a single page or screen surely isn't a good way to express
the presentation order though. On different sized screens and different
sized pages the actual number of lines could vary. I suppose the answer
depends on just what you mean by "line number".

Tax amounts are usually identified by general ledger codes or other
codes at line level - not merely by a positional number. I think that's
what determines their position at the bottom of the document in most
financial systems.

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://www.yqcomputer.com/ (en-US,SQL.90).aspx

 
 
 

Use of 'Line numbers' for certain tables

Post by Arnie Rowl » Mon, 26 Jun 2006 00:39:15

This is one of those myriad of areas where database purists and business
practicalists at time disagree. And it is in the true nature of debate that
we all are 'enlightened' in the process.

There are times, that for various business purposes, each subsequent
presentation of the same document (invoice, statement, etc.) MUST be the
same. Some businesses even have a requirement to 'number' the invoice detail
lines -and that numbering cannot vary between subsequent presentations. In
those situations, there is nothing 'wrong' with adding a sequencing field to
the detail table.

And adding to what David offered, there may be a need for a 'section'
indicator as well. One client needed to have certain customer
specific'disclaimer' information presented in the invoice detail -but always
first. And then there was a need for a 'grouping' by type of product
presented in the remainder of the invoice detail.

Section identifiers and sequencing identifiers most certainly confound the
concepts of what is the nature of the entity captured in the table. But if
we re-examine the entity, perhaps it should be the 'Invoice' rather than the
'Order'. After all, in business we do not manifest an 'order' -without an
invoice. And if the table is modeling the 'Invoice', then clearly section
and sequencing information are allowable attributes of the entity.

As in normailization/de-normalization. We strive toward the 'pure' form
until it hurts, and then back up enough until it works without 'pain'.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam
 
 
 

Use of 'Line numbers' for certain tables

Post by Tracy McKi » Mon, 26 Jun 2006 05:31:52


In all of the billing/invoice/order systems that I've worked with, tax,
freight, etc are not line items on the document. Each line item on the
document has a "sequence" number, indicating its position within the
document, and a flag to indicate its taxable status. The tax amount is
part of the document "header", and is calculated by summing up the
taxable line items and applying the proper tax rates.

I think there are probably as many variations of this as there are
DBA's... :-)
 
 
 

Use of 'Line numbers' for certain tables

Post by John Linvi » Mon, 26 Jun 2006 07:28:11

Many thanks folks for the comments, wasnt quite sure if I had 'missed'
something.

For this type of business the customer sees the detail of the job so as to
able to compare with the written quote.

Account disribution is a seperate table which could have one to many rows
depending on the work done by varius pre-press, press, post-press
departments and subcontracted work .

Also being billed by the same company are newspaper ads IE 3 Col x 95 agate
@ n.nn rate, Ad 'Catch line', added charges, taxes

The ability to reprint an invoice at any point in time in the future is a
requiment.

Once again
Many thanks


--
Life is a journey, wear comfortable shoes
Tom Moreau Ph,D




John Linville
 
 
 

Use of 'Line numbers' for certain tables

Post by --CELKO- » Mon, 26 Jun 2006 09:12:13

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


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

Use of 'Line numbers' for certain tables

Post by John Linvi » Mon, 26 Jun 2006 10:12:16

oe

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
--
Life is a journey, wear comfortable shoes
Tom Moreau Ph,D




John Linville


"--CELKO--" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Use of 'Line numbers' for certain tables

Post by --CELKO- » Mon, 26 Jun 2006 10:53:37

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


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.


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.


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.yqcomputer.com/
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.
 
 
 

Use of 'Line numbers' for certain tables

Post by John Linvi » Mon, 26 Jun 2006 19:16:06

> No inventory? No ink, no paper is consumed? I assume that you mean

In the 25 years I have been associated with the printing and publishing
industry I have never seen an invoice for a printing job list any paper,ink
plates etc only only what the job was, additional options ordered by the
customer, the description lines would vary according to the job, 5,000 #10
envelopes, a10,000 run of a 32 page tab weekly newspaer to a 500,000 run
telephone directories.
The inventory used on a job is the concern of the accounting and job costing
group, which ultimatly affects the estimating program

Joe for the past 20 plus years all my customers have wanted and demanded
that one one invoice form be used.


This is also an industry standard measurment, I know it sounds crazy, but so
is a lot of the printing units of measure, on looking at my 'Printers type
gauge' 72 points is almost 1 foot. How thick is an 8 point card stock?.
Depending on the paper type in roll stock the area varies in a ream to
arrive at the 'paper weight' Incorperate that when you write a web
estimating application. Something to be said for the metric system of GPM
Grams per Meter sq.

The retention of documents is an iffy subject right now, a lot retain the
invoices in the accounting system even though
Revenue Canada says no, also you must keep records for seven years and only
destroy with their permission. Caught between the proverbial rock and a hard
place.

--
Life is a journey, wear comfortable shoes
Tom Moreau Ph,D




John Linville