how to make static ids dynamic in sql?

Question

I've a problem now that I've a table that contains all the fields of a specific system, the table contains the fieldID that is use statically in the real system later on and unfortunately the fieldID is auto increment.

The problem is that each user of a system can create fields dynamically and these fields are created in the same table with auto increment IDs.

Now we want to add new system static fields to be added statically in the solution like the main values that were created in the beginning of the project.

But each user uses the system create some custom fields so the fieldID in each user's system is different.

How can I add some new static IDs without editing in other user's IDs?

There was more than one idea:

1. Start making IDs with -ve values but searched online and found it's a bad practice.

2. Add new column to add new IDs but we'll have difficulties that we'll use the FieldID can't use the other column in the architecture.

Any better solutions?

Answer 1

I've seen Databases has "INT" Identity column and yet I haven't seen anyone use negative numbers. I don't know if its right for you or not, but I suggest you assign for example (0) OR (-1) to your static field. most of the time database designers use IDENTITY(1,1) so negative numbers never uses in Identity column.