M
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
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