Had I been asked I would have recommended against adding the last sentence to
the previous posting, but the other points are valid. My understanding of
your proposed structure is different from Klatuu's, but in either case there
are literally no arguments in favor of your approach in a relational database.
It would make sense only in a flat database such as Excel. When a flat
database is called for Excel is generally the better tool for the job, but a
flat databse is not called for here.

Are you planning to copy the invoice number and other invoice header
information to each invoice item record? If so, is the only reason that you
believe it would be less overhead than maintaining two separate tables?

There are any number of objections to such an approach, one of which is that
overhead is likely to be greater, not less. You would not throw all of your
paper files into a large crate in the belief that a single container is less
overhead than a series of file drawers. The analogy is imprecise, but still
relevant.

Among the problems I see is that you do not have a ready way to view a single
invoice's information. One way would be to scroll through all of the records
until you notice the number you want, then take note of when the number
changes as you continue to scroll through the records. Another way would be
to filter the recordset (or load a selective recordset) to view a single
invoice. With a filtered recordset you would need to run the filter again to
view the next batch of records. With invoice header information in a single
table you could simply navigate to the next invoice to see all of that
invoice's associated line items. The flat file approach will inevitably lead
to a more awkward user interface.

Another objection would be the possibility of data entry errors. You could
avoid that to some extent by using code to copy the record, but that involves
overhead. Another is that you cannot enforce unique invoice numbers at the
table level. You could use code to check whether an invoice number has been
used for a different customer on a different date or something, but again
that is a lot of overhead, and probably difficult to construct.

"Only" three fields breaking the redundant data rule is three more than I
would accept. There is sometimes room for debate about what constitutes
redundant data, but not in this case. Adding a related table is easy, and
does not involve "maintenance" after it is created. Coding for a flat
structure is where the overhead really starts to add up.

wrote:
"What would be wrong with that?"
Almost everything.
[quoted text clipped - 25 lines]

- Show quoted text -

Wow, I feel as though I have been flamed. This is no joke. It was
only a question on which I wanted advice. I said the invoicing
function was simple, not the database itself.

Fields in the one table invoicing transactions I envisage would be:

invoice date
invoice no
customerID
transaction date
transaction ref
productID
item description
Quantity
Unit price

As I see it, only the first 3 would break the redundant data rule.
The data for the invoice report (including the header) would be built
on the fly (using VBA) at time of invoice creation, drawing data not
just from the invoice transaction table but also the related tables on
customers/products etc.

But thanks for responding - at least you confirmed the only objection
to a one table design for invoice transactions is the data redundancy
rule which in this particular case is not a great overhead if it saves
having to maintain and update 2 tables.

--
Message posted via http://www.accessmonster.com