3
I have a Windows Form application developed in C# and with SQL database.
One of the functions of the system is to carry out SALE for several different COMPANIES and in several different POS (point of sale), however, using a single database. I use ID to control the sale it is bigint AUTO_INCREMENT
in the database.
Problem: Company 1 makes sale 1, 3, and 5 company 2 makes sale 2, 4, 6 the customer thinks the system is wrong because, is not following a sequential sale order.
Then I thought about putting Co. Company + Sales Number.
However, the company is recorded in the same client table so I can easily a company with the code 9000 and I can have a sale with the code 10000 I’m afraid of then this field blind. What kind of data could I use to prevent this?
Problem 2: I have a registered company 1 and 11 the system can be lost when making the sale when company 1 makes the sale 11 or when company 11 makes the sale 1.
How I could solve this problem?
Bigown, if I use the composite key I will not have this problem: I have a registered company 1 and 11 the system may get lost while performing the sale when Company 1 makes the sale 11 or when Company 11 makes the sale 1.
– Tozzi
No, it does not run, company code is one thing, sale code is another. If did and occurred, did something wrong.
– Maniero
@mustache is right. This will occur if you do sums of integers, for example 11 + 1 = 12 and 1 + 11 = 12, but if you concatenate with string it is 1 & 11 = 111 and 111 & 1 = 1111. Look at my example in my answer.
– user26552
@Murilo but I’m talking about composite key, neither sums numbers nor concatenates texts. The concatenation would occur in the third option I put and would work also.
– Maniero
@bigown, how do I do this "replacement key and it’s the most common to do. To show the user there will be another sequential numbering control. Need to have a control table of this per company and need access and increment in atomic operation not to create duplicity."
– Tozzi
@Lilloh It is a process that involves several parts of the code, it is not easy to put in an answer, even worse in a comment. The answer from Cantoni shows a part of this, despite being in Groovy and I don’t know if you need to do all that. Think you have two dice. One of them the
ID
which I think knows how to handle, it is primary key, your system works with it. The other one is given as any other that shows to the user. Actually there are two columns, one of them is the company. I think it’s okay to do that. The other is sequential code controlled by you. There is a sequence for each company.– Maniero