November 18, 2015 at 2:09 pm
ScottPletcher (11/18/2015)
First, how do you know that inactive codes are routinely searched? That's almost never the case.
Oh really?
I cannot think of an application when it's not the case.
When people need to generate unique codes?
Mobile top up numbers. Gift cards. iTunes cards. Game access codes. Software activation codes.
In any of these cases I should not be able to get credit by re-entering a previously used code in any foreseen future.
Don't you think?
Therefore in all these cases previously used (means not active anymore) codes must be prevented from being generated as new and active ones. Therefore you need to lookup for already issued inactive codes (as well as active) to make sure a new one matches any of them.
Now - name me a single case when knowing inactive unique codes is not important.
Besides, the lookup is based on the Code value, not the ID value. Yet you're so mentally fixated on the idea of an identity that even when it is never used in any sample lookup given you still insist on clustering using it. Because of that, you're then forced to store the entire table again as a nonclustered index that can actually be used for lookups. That's a horrible -- if sadly often typical -- waste of resources.
Let's not discuss each others mentality.
Because I can say in reply that you are so mentally limited by you "tunnelling vision" to the only query you see in front of you that you cannot imagine the whole picture.
But I don't like to say that, so I won't.
Instead of it I'll patiently explain the point which you miss again and again.
Can you write a query for distributing next, say, 10 unique codes to the customers?
You need to select top 10 active codes, the codes must be in random order (a customer getting one of the codes should not be able to predict the next one issued) - which would be an order of their appearance, as they've been generated, and then you need to update the status of those issued codes to "Inactive".
ID is the piece of data which preserves the order of appearance of the codes.
It actually carries quite important information in this case (important not for codes generation but for issuing codes to the public), so by all means it's not a waste of resources.
It actually saves resources, because it allows a "give away" query to read just a small set from a clustered index (1 or 2 data pages containing those 10 codes you need to present) and then it allows to update IsActive status without causing any index fragmentation - because the order or records in the clustered index (IsActive, ID) after update will remain the same as before update.
Here is a small code snippet to simplify it for you:
SELECT TOP 10 RandomCode, ID
INTO #Out
FROM CodesTable
WHERE isActive = 1
ORDER BY ID -- selecting in the order of rows in the clustered index, and in the same time in a random order of codes appearance
UPDATE CodesTable
SET IsActive = 0
WHERE IsActive = 1
AND ID <= (SELECT MAX(ID) FROM #Out)
-- we know that the selected ID's are all sequential and they are all first ID's in sequence of still active ones.
-- only records within a single data page or within 2 consecutive ones are updated,
-- and their order within clustered index remain the same after update - no rewriting records, no fragmentation.
_____________
Code for TallyGenerator
November 18, 2015 at 2:49 pm
Sergiy (11/18/2015)
ScottPletcher (11/18/2015)
First, how do you know that inactive codes are routinely searched? That's almost never the case.Mobile top up numbers. Gift cards. iTunes cards. Game access codes. Software activation codes.
In any of these cases I should not be able to get credit by re-entering a previously used code in any foreseen future.
Don't you think?
Of course. But that doesn't mean I have to store decades worth of obsolete numbers in my main table, now does it? If the number is not in the active table, I can't use it to buy anything. I don't care if that number happened to be valid 10, 20, 30 or 40 years ago, all I care about is that it has no value now.
Now why do you think retailers were reducing the value of old cards, so much per month (until it became basically illegal)? So that the card would go to 0 and not have to be stored in the live comparison table.
For all your pompousness, you're very ignorant about basic processing, and way worse about index tuning.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 18, 2015 at 2:59 pm
ScottPletcher (11/18/2015)
Of course. But that doesn't mean I have to store decades worth of obsolete numbers in my main table, now does it? If the number is not in the active table, I can't use it to buy anything.
If obsolete numbers are deleted from the main table - what stops the generating utility to re-introduce them again as fresh and active ones?
And then users to re-use the old codes without buying it?
_____________
Code for TallyGenerator
November 18, 2015 at 3:04 pm
Sergiy (11/18/2015)
ScottPletcher (11/18/2015)
Of course. But that doesn't mean I have to store decades worth of obsolete numbers in my main table, now does it? If the number is not in the active table, I can't use it to buy anything.If obsolete numbers are deleted from the main table - what stops the generating utility to re-introduce them again as fresh and active ones?
And then users to re-use the old codes without buying it?
Lol. So you think Amazon.com has every gift code number they've ever issued in their current gift codes table? Unbelievable.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 18, 2015 at 3:46 pm
ScottPletcher (11/18/2015)
Lol. So you think Amazon.com has every gift code number they've ever issued in their current gift codes table? Unbelievable.
Why not?
May be not "ever issued", may be "within last N years" - I don't know.
There are more things in heaven and earth, Horatio,
Than are dreamt of in your philosophy.
What I know for sure is there is no point in having unique codes if they are not required to be unique within a range of both used and not yet used codes.
How you implement that uniqueness - it's another question.
If you go with a lookup table - than yes, you have to keep used codes for the whole retention period, as it's defined by the business rules.
_____________
Code for TallyGenerator
November 18, 2015 at 3:55 pm
Sergiy (11/18/2015)
ScottPletcher (11/18/2015)
Lol. So you think Amazon.com has every gift code number they've ever issued in their current gift codes table? Unbelievable.Why not?
May be not "ever issued", may be "within last N years" - I don't know.
There are more things in heaven and earth, Horatio,
Than are dreamt of in your philosophy.
What I know for sure is there is no point in having unique codes if they are not required to be unique within a range of both used and not yet used codes.
How you implement that uniqueness - it's another question.
If you go with a lookup table - than yes, you have to keep used codes for the whole retention period, as it's defined by the business rules.
You keep missing the point. When someone tries to use a card/code, all that has to be determined is if that card/code currently has value, and if so, how much. That requires only active entries. The company determines what entries are in the active table, not the people holding the cards. A person could only (try to) re-activate a card/code by going thru the company, and most of them will simply not do it. They just issue a new card for the returned/disputed amount. Gift codes can't be re-used anyway.
When I helped a restaurant start up, we used this thing called an "identity column" for gift card numbers, so that they will never be repeated. There are a hundred other ways to do this, of course, but they all lead to the same thing: while you probably very much do want to keep a history of gift cards for tracking and analysis purposes, you'd never need to store them in the current, i.e. value-lookup, table. Once they've been used up, you'd remove them so they can't be reused.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 18, 2015 at 5:18 pm
I use to top up my mobile by texting recharge codes to provider's number.
All the numbers I ever used are still sitting in the memory of my phone.
If the provider would not check for uniqueness of the codes against the database of old used codes their code generator would be able to generate one or more of those inactive codes as new and active.
And I would be able to recharge my account by re-submitting those used codes again and again.
Once I reused the codes, they would be removed from the database and quite possibly being generated as new and active again.
And I could recharge my phone by submitting those old codes again.
I do not need to go for any company to resubmit the codes I have submitted before. I already have them.
Therefore the company needs to make sure that those codes which were ever used by anybody do not show up again.
Is it such a difficult concept to understand?
_____________
Code for TallyGenerator
November 18, 2015 at 5:54 pm
ScottPletcher (11/18/2015)
Once they've been used up, you'd remove them so they can't be reused.
How exactly you intend to prevent used codes from being reused (regenerated as new) if they are removed from the database?
And therefore you have no way to tell if a newly generated code was actually used before?
_____________
Code for TallyGenerator
November 19, 2015 at 8:30 am
Sergiy (11/18/2015)
ScottPletcher (11/18/2015)
Once they've been used up, you'd remove them so they can't be reused.How exactly you intend to prevent used codes from being reused (regenerated as new) if they are removed from the database?
And therefore you have no way to tell if a newly generated code was actually used before?
Again, "identity column". Google "SQL Server identity column". And there a hundred other ways.
But that's only for gift codes. This particular q was about promotion codes. Promotion codes could be used over and over, if the company prefers, assuming the promo codes expire, as (virtually) all do. The company still only needs a list of current codes and their current meanings, period. Why would pizza hut, for example, care what their codes were 20 years ago or what they meant then??
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 19, 2015 at 1:34 pm
ScottPletcher (11/19/2015)
Sergiy (11/18/2015)
ScottPletcher (11/18/2015)
Once they've been used up, you'd remove them so they can't be reused.How exactly you intend to prevent used codes from being reused (regenerated as new) if they are removed from the database?
And therefore you have no way to tell if a newly generated code was actually used before?
Again, "identity column". Google "SQL Server identity column". And there a hundred other ways.
But that's only for gift codes. This particular q was about promotion codes. Promotion codes could be used over and over, if the company prefers, assuming the promo codes expire, as (virtually) all do. The company still only needs a list of current codes and their current meanings, period. Why would pizza hut, for example, care what their codes were 20 years ago or what they meant then??
Now you stopped making sense at all.
Clearly, you don't have an answer on my question, so you bring up some bunch of nonsense.
Can you name me a single company which allows to use promotion codes over and over again?
If that would be the case - why would they bother to check if newly generated code have been generated before?
But before you start with this please answer the question you refused to answer:
ScottPletcher (11/18/2015)
Once they've been used up, you'd remove them so they can't be reused.
How exactly you intend to prevent used codes from being reused (regenerated as new) if they are removed from the database?
_____________
Code for TallyGenerator
November 19, 2015 at 1:40 pm
Sergiy (11/19/2015)
ScottPletcher (11/18/2015)
Once they've been used up, you'd remove them so they can't be reused.How exactly you intend to prevent used codes from being reused (regenerated as new) if they are removed from the database?
I'm actually answered this at least twice. One more time you idiot, then I'm done with you. If the code entered is not in the current/active table, it is rejected, period. I don't need to know it was 2 years old or 12 years old or 100 years old, I just need to know it's not a current code.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 19, 2015 at 1:57 pm
ScottPletcher (11/19/2015)
Sergiy (11/19/2015)
ScottPletcher (11/18/2015)
Once they've been used up, you'd remove them so they can't be reused.How exactly you intend to prevent used codes from being reused (regenerated as new) if they are removed from the database?
I'm actually answered this at least twice. One more time you idiot, then I'm done with you. If the code entered is not in the current/active table, it is rejected, period. I don't need to know it was 2 years old or 12 years old or 100 years old, I just need to know it's not a current code.
I see you frustration.
It's so difficult for to accept being, well, not so smart.
But can you understand your own sentence?
Once they've been used up, you'd remove them so they can't be reused.
My question you refuse to answer for 3rd time:
What should prevent Once used up and removed codes from being reused?
Do you see the scenario how they can be reused?
Or should I explain it for you 3rd time?
_____________
Code for TallyGenerator
November 19, 2015 at 2:35 pm
Sergiy (11/19/2015)
ScottPletcher (11/19/2015)
Sergiy (11/19/2015)
ScottPletcher (11/18/2015)
Once they've been used up, you'd remove them so they can't be reused.How exactly you intend to prevent used codes from being reused (regenerated as new) if they are removed from the database?
I'm actually answered this at least twice. One more time you idiot, then I'm done with you. If the code entered is not in the current/active table, it is rejected, period. I don't need to know it was 2 years old or 12 years old or 100 years old, I just need to know it's not a current code.
I see you frustration.
It's so difficult for to accept being, well, not so smart.
But can you understand your own sentence?
Once they've been used up, you'd remove them so they can't be reused.
My question you refuse to answer for 3rd time:
What should prevent Once used up and removed codes from being reused?
Do you see the scenario how they can be reused?
Or should I explain it for you 3rd time?
When the card is used up, it's removed forever from the current table, since it has no current value. The system will accept only cards in the current table, period. No person outside the company can possibly put it back into the current table. So how would they ever be able to reuse it? It's just not possible. How do they get around that and re-use the card? How??????
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 19, 2015 at 2:54 pm
ScottPletcher (11/19/2015)
Try to follow this, it's simple enough even for you.When it's used up initially, it's removed forever from the current table, since it has no current value.
No person outside the company can possibly put it back into the current table. So how would they ever be able to reuse it? They simply can't.
LOL!
Do you really think Amazon has already generated all the promo codes they are gonna use in any foreseen future?
And they do not generate new codes anymore?
😛
OK, I see what you cannot understand.
Promo codes are not issued all at once.
Got it?
In other words:
Promo codes are issued in bunches sufficient for each particular promo campaign.
And each bunch of promo codes is meant to be used for that particular campaign it was issued for.
When one campaign is over all the relevant promo codes are made inactive.
And when and another one is about to start they issue a new bunch of promo codes.
But no business would want customers to use old promo codes in the new campaign.
Therefore they want to make sure the bunch of newly issued and activated codes contain no codes from old and closed campaigns.
For that - they need to be able to check if a new code has been used ever before, in DB terms - is it in the list of ever issued codes, active or inactive.
Got it now?
_____________
Code for TallyGenerator
November 19, 2015 at 2:54 pm
I'd ask both of you to take a breath, calm down, and cease insulting each other. If you can't do that, then please stop posting. This discussion isn't worth personal attacks.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply