Table Design

[Maria]

I have to track customers and Merchandise Credits issued
to them. We have 15 stores and any store can issue a
credit and it can be redeemed at any other store.
Further, if there's $10 or more "left" on the merchandise
credit after the shopping, we reissue another one.

We are trying to track the original credit all the way to
full redemption (or less than $10 we give back cash).

When reissued, we get a new transaction number, date and
amount. Trans# and date will probably be unique, but no
guarantee across all stores. So, I created 3 tables:

1. Customer - AutoNumID, name & address & phone & then
I have a field "Still Active" to indicate whether this M/C
has been depeleted or not. This seems to be a field that
will be hard to update properly, or at all. The table
gives us the chance to assign the same Key (ID) to the
same person if they get another M/C (and recognize
customers who get lots of M/Cs over time)

2. Issued MC - AutonumID, DateofIssue, StoreNumber,
Customer # (key to custtable), Transaction #, Amount of
Merchandise Credit, and then I have a field "Reissued?"
(yes or no). Again, the YES/NO field seems to me to be in
question. It's easy to update it, but I'm just not sure
of its value.

3. Redeemed MC - AutonumID, Customer#(key to custtable),
M/CKey (key to IssuedMC table), StoreNum, RedemptionDate,
Reissue? (yes or no), Transaction# - only if reissued,
Amount - only if reissued.

I can see this isn't the right design, I just don't know
what to do. I can see that if the reissued MC from
RedeemedMC table is redeemed later, and/or reissued again,
I have no way to track that.

It's sort of history on the MC, I guess. The stores will
send me whatever information I need in Excel and I'll
import it into the database or have people key it in ( I
will have to create forms for that).

Thanks - I hope this is enough information. I'm kinda new
to all this (4 months or so).
Maria

 

[Jeff Boyce]

Maria

Not sure I completely understand your business practice, so the suggestions
may not fit...

Whether a MC is the original, or a re-issue, based on $10+ left, it is still
a MC, right? What I don't understand is the business need for creating a
new MC, rather than showing the amount left on the old one.

When a MC is redeemed, it is for some amount, right?

If I got both of those correct, then another way to look at your data would
be that you have customers (with unique customer#s), and Merchandise Credits
(with unique MC#s).

A customer is a customer, whether or not s/he ever gets a MC. Only keep
customer info in the customer table (name, address, phone), nothing about
MCs.

The MC is ONLY valid if awarded to a customer, right? It isn't like you
keep piles of MCs around, like piles of $20 bills.

So another design for MC might be:

trelMCAwarded
MCAwardedID (autonumber, primary key)
CustomerID (foreign key, from your tblCustomer)
DateAwarded
AmountAwarded
StoreAwardedAt
[opt.] SalesStaffWhoAwards
DateRedeemed
AmountRedeemed
StoredRedeemedAt
[opt] SalesStaffWhoRedeems
MCAwardReissued (this would hold the MCAwardedID of the NEW MC you
reissue to cover the more-than-$10 situation).

This design should handle a "single-use" MC model, which I think you are
describing.

If you actually have (or decide to have) one-MC, multiple uses (until
"empty"), you need to break out the Redeemed dates and amounts and at-store
to a separate table, as you would have a one-to-many relationship between
the MCAwarded and Redemption events.

This would move the three (or four) "Redeem"-oriented fields out to a new
table:

trelRedeemedMC
RedeemedMCID (autonumber, primary key)
MCAwardedID (foreign key, from trelMCAwarded)
DateRedeemed
AmountRedeemed
StoredRedeemedAt
[opt] SalesStaffWhoRedeems

and remove them from the trelMCAwarded table.

Or did I go completely off the tracks on this?

 

[Maria]

I'm going to talk to the users on Tuesday - you ask good
questions here - I am not totally sure what they really
want to accomplish. I'll be back to you!
Thanks
Maria

 

[Jeff Boyce]

Consider posting back to the 'group -- more eyes will check a fresh post
than one already "answered".

Jeff

I'm going to talk to the users on Tuesday - you ask good
questions here - I am not totally sure what they really
want to accomplish. I'll be back to you!
Thanks
Maria

-----Original Message-----
Maria

Not sure I completely understand your business practice, so the suggestions
may not fit...

Whether a MC is the original, or a re-issue, based on $10+ left, it is still
a MC, right? What I don't understand is the business need for creating a
new MC, rather than showing the amount left on the old one.

When a MC is redeemed, it is for some amount, right?

If I got both of those correct, then another way to look at your data would
be that you have customers (with unique customer#s), and Merchandise Credits
(with unique MC#s).

A customer is a customer, whether or not s/he ever gets a MC. Only keep
customer info in the customer table (name, address, phone), nothing about
MCs.

The MC is ONLY valid if awarded to a customer, right? It isn't like you
keep piles of MCs around, like piles of $20 bills.

So another design for MC might be:

trelMCAwarded
MCAwardedID (autonumber, primary key)
CustomerID (foreign key, from your tblCustomer)
DateAwarded
AmountAwarded
StoreAwardedAt
[opt.] SalesStaffWhoAwards
DateRedeemed
AmountRedeemed
StoredRedeemedAt
[opt] SalesStaffWhoRedeems
MCAwardReissued (this would hold the MCAwardedID of the NEW MC you
reissue to cover the more-than-$10 situation).

This design should handle a "single-use" MC model, which I think you are
describing.

If you actually have (or decide to have) one-MC, multiple uses (until
"empty"), you need to break out the Redeemed dates and amounts and at-store
to a separate table, as you would have a one-to-many relationship between
the MCAwarded and Redemption events.

This would move the three (or four) "Redeem"-oriented fields out to a new
table:

trelRedeemedMC
RedeemedMCID (autonumber, primary key)
MCAwardedID (foreign key, from trelMCAwarded)
DateRedeemed
AmountRedeemed
StoredRedeemedAt
[opt] SalesStaffWhoRedeems

and remove them from the trelMCAwarded table.

Or did I go completely off the tracks on this?

--
Good luck

Jeff Boyce
<Access MVP>

.

 

[Jim Davenport]

I have to track customers and Merchandise Credits issued
to them. We have 15 stores and any store can issue a
credit and it can be redeemed at any other store.
Further, if there's $10 or more "left" on the merchandise
credit after the shopping, we reissue another one.

We are trying to track the original credit all the way to
full redemption (or less than $10 we give back cash).

When reissued, we get a new transaction number, date and
amount. Trans# and date will probably be unique, but no
guarantee across all stores. So, I created 3 tables:

1. Customer - AutoNumID, name & address & phone & then
I have a field "Still Active" to indicate whether this M/C
has been depeleted or not. This seems to be a field that
will be hard to update properly, or at all. The table
gives us the chance to assign the same Key (ID) to the
same person if they get another M/C (and recognize
customers who get lots of M/Cs over time)

2. Issued MC - AutonumID, DateofIssue, StoreNumber,
Customer # (key to custtable), Transaction #, Amount of
Merchandise Credit, and then I have a field "Reissued?"
(yes or no). Again, the YES/NO field seems to me to be in
question. It's easy to update it, but I'm just not sure
of its value.

3. Redeemed MC - AutonumID, Customer#(key to custtable),
M/CKey (key to IssuedMC table), StoreNum, RedemptionDate,
Reissue? (yes or no), Transaction# - only if reissued,
Amount - only if reissued.

I can see this isn't the right design, I just don't know
what to do. I can see that if the reissued MC from
RedeemedMC table is redeemed later, and/or reissued again,
I have no way to track that.

It's sort of history on the MC, I guess. The stores will
send me whatever information I need in Excel and I'll
import it into the database or have people key it in ( I
will have to create forms for that).

Thanks - I hope this is enough information. I'm kinda new
to all this (4 months or so).
Maria

Maria,

Get back to me. I already know of a Shopping Cart
that has a Gift Certificate feature which can handle
the entire process almost off the shelf using Access
or SQL. Your stores can log into your ASP web app
and Issue a "Gift Certificate" (renamed to be a "Store
Credit") and when all or part of the "Gift Certificate" is
used up, they just enter the transaction amount, the client
Name and Address and the Gift Certificate Number and the
system takes care of all the math and the audit trails.

John B