Database accuracy

[TomB]

I'm wondering how others maintain the accuracy of their databases.

For example, if you have an invoice for customer 100, then three years later
customer 100 moves to a new building. How do you keep the shipping address
correct for the original invoice?

My boss suggests maintaining "transaction" table(s) (that's what he calls
it) which pulls the information from the individual tables. So the table(s)
would contain information like.....

Date Company Ship To
Invoice #
2003-08-11 Joe Blow's Company 123 SomeStreet 123456

and the invoice table would have the products in text format like.....

Invoice# Product Desc Qty
123456 987 blah blah 2
123456 876 blah blah 1

Is this the best approach? Doesn't this waste a fair bit of server
resources?

My approach is to have an "Active" field for a table, and rather than
changing the data, I just mark the original as inactive and create a new
record marked active.

Thanks
Tom

[Bob Barrows]

Are you storing that information in the invoice record? Why wouldn't the
invoice record contain a foreign key to a customers table which contains the
Shipto info? Do you need to know the original shipto information whenever
you view the invoices?

I would not recommend maintaining multiple tables with the same information
unless you were creating a data warehouse.

Bob Barrows

TomB wrote:

> I'm wondering how others maintain the accuracy of their databases.
>
> For example, if you have an invoice for customer 100, then three
> years later customer 100 moves to a new building. How do you keep
> the shipping address correct for the original invoice?
>
> My boss suggests maintaining "transaction" table(s) (that's what he
> calls it) which pulls the information from the individual tables. So
> the table(s) would contain information like.....
>
> Date Company Ship To
> Invoice #
> 2003-08-11 Joe Blow's Company 123 SomeStreet 123456
>
> and the invoice table would have the products in text format like.....
>
> Invoice# Product Desc Qty
> 123456 987 blah blah 2
> 123456 876 blah blah 1
>
> Is this the best approach? Doesn't this waste a fair bit of server
> resources?
>
> My approach is to have an "Active" field for a table, and rather than
> changing the data, I just mark the original as inactive and create a
> new record marked active.
>
> Thanks
> Tom

[Manohar Kamath [MVP]]

Tom,

One way to do this, is separate Addresses as their own entities -- since
more than one person could have lived in the same address over a time
period.

Then, store the Address ID in the individual invoice record, rather than in
Customer table. The customer could have a address ID associated as well,
perhaps in a customerAddress table, where there is a many-many relationship.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"TomB" <shuckle@hotmail.com> wrote in message
news:Ofk1aaBYDHA.3924@tk2msftngp13.phx.gbl...

> I'm wondering how others maintain the accuracy of their databases.
>
> For example, if you have an invoice for customer 100, then three years

later

> customer 100 moves to a new building. How do you keep the shipping

address

> correct for the original invoice?
>
> My boss suggests maintaining "transaction" table(s) (that's what he calls
> it) which pulls the information from the individual tables. So the

table(s)

> would contain information like.....
>
> Date Company Ship To
> Invoice #
> 2003-08-11 Joe Blow's Company 123 SomeStreet 123456
>
> and the invoice table would have the products in text format like.....
>
> Invoice# Product Desc Qty
> 123456 987 blah blah 2
> 123456 876 blah blah 1
>
> Is this the best approach? Doesn't this waste a fair bit of server
> resources?
>
> My approach is to have an "Active" field for a table, and rather than
> changing the data, I just mark the original as inactive and create a new
> record marked active.
>
> Thanks
> Tom
>
>

[TomB]

Yes.... That makes sense.
I was thinking I would just store the CustomerID, and the Customer Table
would have the address information. But if I have both the CustomerID and
the AddressID in the Invoice header table--problem solved.

Very good, thanks.


"Manohar Kamath [MVP]" <mkamath@TAKETHISOUTkamath.com> wrote in message
news:unhA%23zBYDHA.1816@TK2MSFTNGP09.phx.gbl...

> Tom,
>
> One way to do this, is separate Addresses as their own entities -- since
> more than one person could have lived in the same address over a time
> period.
>
> Then, store the Address ID in the individual invoice record, rather than

in

> Customer table. The customer could have a address ID associated as well,
> perhaps in a customerAddress table, where there is a many-many

relationship.

>
> --
> Manohar Kamath
> Editor, .netBooks
> [url]www.dotnetbooks.com[/url]
>
>
> "TomB" <shuckle@hotmail.com> wrote in message
> news:Ofk1aaBYDHA.3924@tk2msftngp13.phx.gbl...

> > I'm wondering how others maintain the accuracy of their databases.
> >
> > For example, if you have an invoice for customer 100, then three years

> later

> > customer 100 moves to a new building. How do you keep the shipping

> address

> > correct for the original invoice?
> >
> > My boss suggests maintaining "transaction" table(s) (that's what he

calls

> > it) which pulls the information from the individual tables. So the

> table(s)

> > would contain information like.....
> >
> > Date Company Ship To
> > Invoice #
> > 2003-08-11 Joe Blow's Company 123 SomeStreet 123456
> >
> > and the invoice table would have the products in text format like.....
> >
> > Invoice# Product Desc Qty
> > 123456 987 blah blah 2
> > 123456 876 blah blah 1
> >
> > Is this the best approach? Doesn't this waste a fair bit of server
> > resources?
> >
> > My approach is to have an "Active" field for a table, and rather than
> > changing the data, I just mark the original as inactive and create a new
> > record marked active.
> >
> > Thanks
> > Tom
> >
> >

>
>

[TomB]

OK, I see what you're suggesting, that too should work.

Thanks Bob

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:%23uRyJqCYDHA.2152@TK2MSFTNGP09.phx.gbl...

> Then you need to add an EffectiveDate, and perhaps a DiscontinueDate to

the

> customer table. That will allow you to tie the invoice records to the
> appropriate customer records, using the InvoiceDate.
>
> Bob Barrows
>
> TomB wrote:

> > I think you misunderstood. My instinct would be to do it as you
> > suggest... Invoice table
> > InvoiceID
> > CustomerID
> > etc.
> >
> > Customer Table
> > CustomerID
> > Address.....
> >
> > If the Customer Address were to change, then all old invoices would
> > show the incorrect Shipping information.
> >
> > And yes, I would need to know the original shipto information for such
> > things as statistics and reports.
> >
> >
> > I think Manohar came up with the solution for me (which should have
> > been obvious) wherein I should keep the addresses as a separate
> > entity from the Customers.
> >
> > Thanks for the help
> >
> >
> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:eXVHxiBYDHA.1784@TK2MSFTNGP09.phx.gbl...

> >> Are you storing that information in the invoice record? Why wouldn't
> >> the invoice record contain a foreign key to a customers table which
> >> contains the Shipto info? Do you need to know the original shipto
> >> information whenever you view the invoices?
> >>
> >> I would not recommend maintaining multiple tables with the same
> >> information unless you were creating a data warehouse.
> >>
> >> Bob Barrows
> >>
> >> TomB wrote:
> >>> I'm wondering how others maintain the accuracy of their databases.
> >>>
> >>> For example, if you have an invoice for customer 100, then three
> >>> years later customer 100 moves to a new building. How do you keep
> >>> the shipping address correct for the original invoice?
> >>>
> >>> My boss suggests maintaining "transaction" table(s) (that's what he
> >>> calls it) which pulls the information from the individual tables.
> >>> So the table(s) would contain information like.....
> >>>
> >>> Date Company Ship To
> >>> Invoice #
> >>> 2003-08-11 Joe Blow's Company 123 SomeStreet 123456
> >>>
> >>> and the invoice table would have the products in text format
> >>> like.....
> >>>
> >>> Invoice# Product Desc Qty
> >>> 123456 987 blah blah 2
> >>> 123456 876 blah blah 1
> >>>
> >>> Is this the best approach? Doesn't this waste a fair bit of server
> >>> resources?
> >>>
> >>> My approach is to have an "Active" field for a table, and rather
> >>> than changing the data, I just mark the original as inactive and
> >>> create a new record marked active.
> >>>
> >>> Thanks
> >>> Tom

>
>

[Manohar Kamath [MVP]]

Jeff,

Even if the context is different, the data is redundant -- since the address
itself does not change. It is not like say a person's name, which might
change, the address pretty much remains the same -- unless the name of the
city/zip changes.

--
Manohar Kamath
Editor, .netBooks
[url]www.dotnetbooks.com[/url]


"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:3f390710.344839092@news.easynews.com...

> On Mon, 11 Aug 2003 11:54:44 -0500, "Manohar Kamath [MVP]"
> <mkamath@TAKETHISOUTkamath.com> wrote:
>

> >What I suggested is just ONE way to do this. You could even store the

entire

> >customer address from the customer table in the Invoice table. So, even

if

> >the customer address in the customer table changes, the invoice address

does

> >not. However, this leads to redundant data, but is very simple to

implement.

>
> However, the data *isn't* redundant, since the context is different.
> The address doesn't apply to the customer in this case, it applies to
> the specific invoice.
>
> I've seen it two ways. One is the bill to and ship to information is
> in the invoice detail table. The other is as mentioned, with an
> addresses table and an AddressID assigned to the proper fields in the
> relevant tables where the data may need to be. There is still an
> address associated to the invoice, as well as the customer. The
> customer record associates the AddressID with the current address, the
> invoices with the AddressID at the time of invoice.
>
> If you're concerned about database size, you run an annual (monthly,
> whatever) process to archive old closed invoices to a separate table,
> posibly on another server, then back it up and remove it or otherwise
> archive as needed.
>
> Jeff