Actually, the way it will probably work is that there is a location
table which includes all address info, a table handling a many-many
relationship of locations to contacts
Almost: the intersect we need here is between the Address to Customer intersect (as per your description above) and Invoice. Its [one] AtiI-intersect -> [many] Invoice.
Addresses would be intended to be unchanged once entered. I.e. if you
move, the old address record remains in the db, we add a new one, and
we tie them together properly.
Yep, that the right direction. Now put on a logical modellers hat the ask "What's an Address". Short answer "Well, the type of address we are interested here is , say Physical Location: a spot on the planet. One unique identifier: LatitudeLongitude coords. Call this entity Physical Location (abbrev=PL). PL [many]-[one] Postcode/ZipcodeInTheUsa (ZC).
ZC [many]-[one] State, State [many]-[one] Country.
This works for many countries but not all. Check your scope of this system. (Remember that, I spoke of it more than once b4). Is this functional scope of the system to just service countries that adhere to this model. If no, you need to expand the model by replacing PL and State with an entity in another dimension whose key is LocationNamingScheme. This is an attribute of Country, ie each Country is linked to a LocationNamingScheme. Plus add entities for intersecting Location to the hierarchy of AddressComponents that link recursively up the chain to give you one instance of an Address for each Location.
I found my old model of this recently. Its in a dump of Oracle Designer 2000, RDBMS version 7.3.4 or thereabouts. If anyone wants to install an old Oracle and Designer to import it let me know.
Imagine what happens if when start supporting multiple shipto's. How
many addresses do we add to the invoice record?
Just the one that is relevant at the time I guess. Ie use the same sort of
Its [one] AtiI-intersect -> [many] Invoice as per above. This now allows for one Billing Address and one ShipTo Address.
To get really over carried away: It you wanted multiple Addresses of various types on an invoice simply intersect the intersect, with a TypeOfAddress attribute on the new intersect. This is the power of relational design, as this is very easy to model in *before* you start coding. It is probably overkill though in this instance.
Paper trail == documents. Documents should contain all necessary data
in the db to reconstruct all relevant business informaton related to
the invoice. However, for a paperless paper trail, we also need to
store the document. I don't see a way around this.
Nor do I. And that is why many big institutions, having analysed this decades ago, created microfiche systems, then later on systems that scan and save (annotated) graphical images of original documents. There were many reasons: legality issues, and for ease of capture, and if reasonably annotated, eg Customer, invoice number, data, total, could be *reasonably* easily retrieved on the offchance that it is actually ever needed.
ps. For the past several years, whatever accounting system I have used, I have printed all sales invoices to .PDF. The systems and paper are long since gone, but the PDFs are still very easily retrievable via nothing more complicated than path (myCompanyName)/FYyyyy/sales/invoices/yyyy-mmdd-(customer).pdf
Ok. Suppose your logo changes. Suppose someone changes a
template.... These would not be reflected in the new reprinted
version and thus it would not be an exact copy. The only way to do
this is to store a representation of the printed page in the db
(probably as either PDF or Postscript).
Exactly.
While it might be possible to also keep a history of the templates,
logos, etc in the db (and we might want to consider that), we cannot
guarantee that the snapshot is accurate without generating the invoice
as a document and putting that back in the db (we can reasonably
guarantee that with appropriate controls).
Exactly. RDBMS = hammer mentality. Just use PDFs. Its far more accurate, practical and easy to implement.
Hope this makes sense,
I agree 100%