Database design question - Access 2003

[xfile]

Hi:

Appreciate if anyone can help me on the following DB design question.

I wish to have a table which stores all customers' information, say TB1.

And another table, say TB2, for only those "active customers".

TB1 will contain all information of TB2 and those are inactive, which means
they no longer wish to do business or receiving information from us.

For those customers from TB2 in the TB1, data should be consistent.

In summary, for the amount of data, TB1>TB2. For the data of the same
account of TB1 and TB2, they should be the same.

TB2 will be used for daily operation. TB1 will be use to benchmark analysis
and historic records.

So my questions are as follow:

(1) Does this logic make any sense? If not, what would be your suggestions?

(2) If so, can it be done by MS Access, and do I need any other tools or
scripts, or whatever things?

(3) If I do need any tools or scripts, would you kindly point me to the
sources where I can find it? I know that I can try Google for some tips,
but I don't even know what are key words to use for search in this case?


Thanks so much.

 

[Allen Browne]

There is a very simple way to achieve this.

Just add a yes/no field to TB1, named (say), "Inactive".
Check the box when a customer is no longer active.

It is *very* easy to write a query that pulls just the active customers as
the source for a form or report. It is just as easy to compare historical
data. If you like you can even use 2 different forms: one for active
customers, and one for inactive, based on 2 queries. To the user it looks
like they are coming from 2 tables, but you avoid all the pain of trying to
keep 2 sets of records consistent, and copy them around.

BTW, if you do this, make sure you index the Inactive field (Duplicates Ok).
Although the text books often tell you not to index a field that has only a
few values (such as a yes/no field), you are going to be using this field
for criteria constantly, and our experience in Access suggests that it can
give up to an order of magnitude performance gain in some circumstances.

 

[xfile]

Hi:

Thanks for your kind reply.

So in other words for which I really want to apply to the real world use is
that I don't have to really "delete" the records of inactive users from the
table, and only use query to select those "active" for, say, newsletters?

The way I tried this approach (obviously an amateur one) is when a customer
or user wishes to cancel the subscription to our promotion letters, I would
simply let them use a web form which will delete their record in the DB.
That is why I need to use TB2.

And TB2 will be always those "active" ones with transactions and/or
newsletters. However, we can use TB1 for analysis purpose.

So if I understood correctly, you were saying that I just use one table
(TB1) and use a yes/no field to indicate if they are active or not (in the
case of in the mailing list or not or others).

When they choose to cancel, it sends to the field and change the value from
Yes to NO, instead of delete the entire record.

And when we need to do promotional newsletters, we just query those are
"Yes"?

If so, this is wonderful.

But am I understood you correctly?

Thanks again for your reply.,

 

[Joseph Meehan]

Hi:

Thanks for your kind reply.

So in other words for which I really want to apply to the real world
use is that I don't have to really "delete" the records of inactive
users from the table, and only use query to select those "active"
for, say, newsletters?
The way I tried this approach (obviously an amateur one) is when a
customer or user wishes to cancel the subscription to our promotion
letters, I would simply let them use a web form which will delete
their record in the DB. That is why I need to use TB2.

And TB2 will be always those "active" ones with transactions and/or
newsletters. However, we can use TB1 for analysis purpose.

So if I understood correctly, you were saying that I just use one
table (TB1) and use a yes/no field to indicate if they are active or
not (in the case of in the mailing list or not or others).

When they choose to cancel, it sends to the field and change the
value from Yes to NO, instead of delete the entire record.

And when we need to do promotional newsletters, we just query those
are "Yes"?

If so, this is wonderful.

But am I understood you correctly?

Thanks again for your reply.,

You understood correctly and that would have been my advice as well . It
makes any number of things easier.

 

[Allen Browne]

If you have a list of customers, and a list of possible
newsletters/promotionals, and you want a user to be able to opt in/out of
these at will, you need 3 tables:
- Customer table: one record per customer, with a CustomerID primary key;
- Promo table: one record per possible newsletter/promational, with a
PromoID primary key;
- CustomerPromo table: one record for each combination of Customer and Promo
you desire.

The CustomerPromo table with have these 2 fields:
- CustomerID: foreign key to Customer.CustomerID;
- PromoID: foreign key to Promo.PromoID.

Now the customer can add or remove the promotionals they desire. In an
Access database, you would interface that with a subform - one row for each
promo. In a webpage, the interface will depend on the technology you are
using.

If this kind of junction table is a new idea, you can see another example
at:
http://members.iinet.net.au/~allenbrowne/casu-06.html

 

[xfile]

Hi:

Thanks again, and I guess that I got what you're saying.

The customer table (A) is for all basic records of customers. e.g. Fields:
CustomerID (primary key), LastName, FirstName, Email....
Record sampe: 1, Smith, David, abc@syz

Promotion table (B) is for all services and/or products offered. e.g.
Fields: PromoID (primary key), Newseletters, ProductA, ProductB, ....
Record sample: 1, newsletter, ProductA, or 2, newseltter

And the third table, CustomerPromo (C), is the combination of two. e.g.:
Fields: CustomerID, PromoID

And we are going to query based on the combination of third table?

Yes, it is a new idea, and I am still figuring it out. I will visit the
link for more study.

Thanks but I guess this is an excellent approach, except I am not familar
with database so I need to figure it out

Really appreciate.

 

[Allen Browne]

That's basically it, except the Promotion table has a *record* for each type
of product you promote, not a field.

 

[xfile]

Hi:

Thanks and I am still reading your tips at the site.

In addition to the table design, I am also studying the date problem which I
posted earlier.

Seems it's very complicated for the date issue, as I would image, especially
when there is international date involved as I will be in the near future.

But it seems difficult to find an example to cover the entire end-to-end
topic from SQL statements to ASP. I am still trying combine different tips
together.

Anyway, really appreciate your kind help, and I will test to see the
outcome.