Good day-

Let's say I have a shipment table (each record is a shipment).
Each shipment will have an origin and a destination.
The origin/destination can be any customer/vendor address
pair (so, vendor to customer, customer to vendor, vendor
to vendor).

I have the customer and vendor address tables, and I was
thinking that I need to have a pointer in my shipment
table that points to a vendor/customer address record
key.

BUT, the problem is that, the customer/vendor address information
can change, and a shipment needs to always have a reference
to the exact address that was used when the shipment was set
up (even if it's no longer the current/active address).

So, is it proper/appropriate to just save the address information
on each shipment record (not normalized)? Or, save an address
pointer and make sure the software doesn't allow for deleting/
updating addresses. It seems like it'd be ok to save the actual
address data on the shipment record.
(I can't really decide, which is why I'm here

Thanks for any comments folks, and I hope I was thorough
in my explanation; if I'm lacking details, please just hollar.

hoss

On Thu, 16 Oct 2003 18:59:52 GMT, "Hoss" <hoss @ nospam
blowthedotcomoutyourass . com> wrote:

You need to keep the shipping address information from changing, but
that doesn't mean that you need to store the addresses in the same
table as the shipment records.

As to the theory, the shipping addresses (both from and to) are
attributes of a shipment. If you consider that you most likely have
many more shipments than addresses, though, it makes sense to store
the addresses in a separate table to avoid duplication of data.
However, it should be a "write-once-read-many-times" kind of table.

I tend to think that once a customer or vendor relationship is
established, data pertaining to that relationship must be kept for at
least as long as the fiscal authorities require, and possibly longer
if your own requirements dictate. Also, a customer or vendor will
usually have several addresses: a business address, which might be a
PO box; a physical address; an address of the central office; and
several shipping addresses, depending on type of shipment. Once an
address is established, it can have attributes of its own: it is also
possible that more than one company might be registered at the same
address if they share certain facilities.

Therefore, "address", "customer" and "vendor" are independent entities
and should each have their own tables (IMHO) with appropriate
relationships defined, i.e. you must have either a customer or a
vendor in order to have an address. This implies that a shipping
address is always tied to a customer or vendor, even if they have gone
out of business -- you would have a "status" field in the
customer/vendor table which handled this information, but never delete
such a record until you are sure that you have no more use for it.


--
Bob Hairgrove
NoSpamPlease (AT) Home (DOT) com

Thanks for your input Bob... I'm going to investigate going that
route (properly setting up my foreign key relationships so I can
force referential integrity).

Yep, that's how I have it designed; I left out those details because
they didn't really pertain to the "do I save an addr reckey or the
actual addr data", but thanks for the suggestion anyway...

Thanks again for your reply; much appreciated

address that was used when the shipment was set up (even if it's no
longer the current/active address). <<

In the book trade and others, ther are SANs (Standard Adddress
Numbers), but you can build your own:

CREATE TABLE SAN_History (san INTEGER NOT NULL,
from_date DATE NOT NULL,
to_date DATE, -- null means current
name CHAR(35) NOT NULL,
street1 CHAR(35) NOT NULL,
street2 CHAR(35) NOT NULL,
city
state CHAR(2) NOT NULL CHECK (state IN (...)),
zip_code CHAR(5) NOT NULL
CHECK (zipcode SIMILAR TO '[0-9][0-9][0-9][0-9][0-9]'),
...
PRIMARY KEY (san, from_date));

Use this view for current orders:

CREATE VIEW SAN (name, street1, street2, city, state, zip_code)
AS
SELECT name, street1, street2, city, state, zip_code
FROM SAN_History
WHERE to_date IS NULL;

joe.celko (AT) northface (DOT) edu says...



Surely the name of the client doesn't change - it's just their address?



"SIMILAR TO" - what implementation is that, or is it standard?

What about "LIKE" or are they synonyms?


Paul...


--

plinehan__AT__yahoo__DOT__com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.