What kind of data to use for sale ID

Question

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?

Answer 1

Different data

The problem is you’re mixing something internal to the system with something external to the system. It’s even possible to do this, but the application has to work by treating this.

The right thing to do is to do what you’re doing for your internal control, so there’s a ID auto incremented by database. Probably a BIGINT is too big for what you need, but you can leave it if you think you should.

This is called replacement key and is 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.

Composite key

If you don’t want to use a replacement key, use the ID of the enterprise in a column and the ID from the sale in another column and make the table primary key be composed by the two columns together. So you can use a unique non-repeating identifier that serves internal use and meets the external requirement.

I hope a future requirement does not create problems in using this.

Single key

You can still do a column with both IDs, but then I think it would be better to use a type CHAR. No sense putting two numbers together the way you’re thinking. Using text is not ideal, but creating a number by joining digits is completely wrong. I put as alternative, but it is not a good solution in general. I just do not rule out.

Answer 2

I believe it is best to use a primary key generated by SQL itself or a C# GUID to save to the bank and add two columns, one the company code and the other the sequential number of the sale, so when you make a new sale, the key will never be the problem and Voce checks which was the last sale of the company and increments the sequential number.
As the company number and the coupon are separate columns I think Voce should not have the second problem too, since the company Voce can pick up from the logged in user or type in a separate field.

If you do not want to create individual columns or tables for each company, Oce can also use a string as a key, placing a separator between the company and the sales number "company-sale" then separating in the application.

Answer 3

First the bigint is a very large field, it is difficult to burst, has to have many sales even with company code 9000.

bigint: -2 63 (-9.223.372.036.854.775.808) to 2 63-1 (9.223.372.036.854.775.807)

https://msdn.microsoft.com/pt-br/library/ms187745.aspx

You could create a new column for the sale number. Keep the primary key as auto-increment of the database, but you do the manual increment of the sale number by making a Max + 1, display the sale number to the customer, not the code.

Even if you used one sequence you wouldn’t get the sales 11 12 13 14 for company 1, you would need a Quence for each company.

You could make a trigger that will generate the sales code, generating the code = company code + sales Qtde of this company, your codes would be like this:

Company 9000: 90001, 90002, 90003, 90004, 9000125

Company 11: 11, 12, 13, 14, 1125

CREATE TRIGGER  triggerInsertVenda ON tbVenda
INSTEAD OF INSERT AS

SELECT * INTO #tmp FROM inserted
DECLARE @codEmpresa int = (SELECT codempresa FROM inserted)
DECLARE @count int = (SELECT COUNT(*) FROM tbVenda WHERE codEmpresa = @codEmpresa)

UPDATE #tmp SET codigo = convert(int, convert(varchar, @codEmpresa) + convert(varchar, @count + 1))


INSERT INTO tbVenda SELECT * FROM #tmp;

Answer 4

If the sequential sale is something important for the user, then a viable and not very complex option is to create a field in the COMPANY table that will store in which sequence is the sale of the respective company.

If you choose this path, then it is important to increment this field in a way that only one SQL Server process can change it at a time. For this, you can do it the way below. The example is in the Groovy language, but is easily adapted to any other. The important thing is not the language, but the Sqls run to ensure that only one SQL Server process can change the SEQUENCIA field at a time (I removed this from a system where I do just that).

def incrementaSequencia(empresaId) {
      sql.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED")
      sql.execute("BEGIN TRANSACTION")
      def rs = sql.rows("SELECT SEQUENCIA FROM EMPRESA WITH (UPDLOCK) WHERE EMPRESA_ID = :empresaId",[empresaId:empresaId])
      def sequencia = (int)rs.first().SEQUENCIA + 1
      sql.executeUpdate("UPDATE EMPRESA SET SEQUENCIA = " + sequencia + " WHERE EMPRESA_ID = :empresaId",[empresaId:empresaId])
      sql.execute("COMMIT")
      return sequencia
}

Note that in this example I need the sequence generated in my program, so I run SELECT in the above instructions. Depending on the case, just increment the field with an UPDATE. Something like this:

      sql.executeUpdate("UPDATE EMPRESA SET SEQUENCIA = SEQUENCIA + 1 WHERE EMPRESA_ID = :empresaId",[empresaId:empresaId])

Now just create a SEQ_VENDA field in the SELL table and save the generated value, IE, each sale you call the routine that generates the ID and save this value in the SEQ_VENDA field.

About the field type, I always use bigint for numerical Ids.