Different data

The problem is that you are mixing something inside the system with something external to the system. You can even do this, but the application has to work fine.

The most correct thing seems to be doing what you are doing for your internal control, so it has a% auto_created by the database. Probably a ID is too large for what you need, but you can leave it if you feel you should.

This is called a substitute key and is the most common one to do.

To show the user there will be another sequential numbering control. You need to have a control table of this per company and need to access and increment in atomic operation to avoid duplication.

Composite key.

If you do not want to use a substitute key, use the BIGINT of the company in one column and ID of the sale in another column and make the primary key of the table be composed of the two columns together. So you can use a unique identifier that does not repeat that is for internal use and meets the external requirement.

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

Unique key

You can still do a column with the two ID s, but then I think it would be best to use a ID type. It does not make sense to put two numbers together in the way you're thinking. Using text is not ideal, but creating a number by adding digits is completely wrong. I put it as an alternative, but it is not a good solution overall. I just do not rule it out.

I think it's best if you use a primary key generated by SQL itself or a C # GUID to save in the database and add two columns, one the company code and another the sequential number of the sale, so when you make a new sale , the key will never be the problem and you check which was the last sale of the company and increase the sequential number. As the company number and the coupon are separate columns I think you should not have the second problem as well, since the company you can get the user logged in or type in a separate field. If you do not want to create the individual columns or tables for each company, you can also use a string as a key, by placing a separator between the company and the company-sale number, then separating the application.

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

bigint : -2 ^ 63 (-9.223.372.036.854.775.808) at 2 ^ 63-1 (9,223,372,036,854,775,807)

link

You could create a new column for the sales number. Keep the primary key as auto-increment of the database, however you manually increment the sales number by making a Max + 1, display the sales number for the customer, not the code.

Even if you used a sequence you would not be able to sales 11 12 13 14 for company 1, you would need a sequence for each company.

You could make a trigger that will generate the sales code, generating the code = company code + sales code of that company, its codes would look 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;

If the sales sequence is something important to 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 the sale of the respective company is.

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 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 SEQUENCE 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 SALES table and save the generated value, that is, at each sale you call the routine that generates the ID and stores this value in the SEQ_VENDA field.

On the field type, I always use bigint for numeric IDs.