Creating empty objects in db or saving them later

Question

I have the following table in my db:

CREATE TABLE document (
  id INT PRIMARY KEY AUTOINCREMENT,
  productModelId INT NOT NULL,
  comment VARCHAR(50),
  CONSTRAINT FK_product_model FOREIGN KEY (productModelId) REFERENCES product_model(id),
)

Of course, real table is much more complicated, but this is enough to understand the problem.

Our users want to see the number of the document when they click button "new". So, in order to do that, we have to create object in db and send to client that object. But, there is a problem. We need to know productModelId before we save the object in db. Otherwise we will have an sql exception.

I see two possible variants (both are ugly, really):

1. To show modal list with product models to user and after that create object in database with productModelId chosen by user.

2. To create a temporary number and after that to save the object in db when user finishes editing the document and saves id. We also need to remove NOT NULL case and validate this somwhere in code.

The first way is bad because we have too much modals in our application. Our UI is too heavy with them.

The second variant is ugly because our database is not consistent without all the checks.

What can you suggest we do? Any new solutions? What do you do in your apps? May be some UI tips. We are using the first variant at the moment.

Answer 1

Theory says that the id you use on your database should not be a relevant information, so the user should not see it if not well hidden in an URL or similar, so you should not display it to the user, and the problem you have is one possible confirmation of this theory.

Right now the solution you have is partially correct: it satisfies technical requirements, but is still bad because if the user doesn't complete the insert you'll end up with the DB having empty records (meaning, with ID and foreign key ok, but all other fields empty or with useless default values), so you are basically circumventing the database validations.

There are two better solutions, but both require you to review your database.

The first is not to use the id as something to display to the user. Use another column, with another "id", declare it unique on the database, generate it at application, display it to the user, and then use this other "id" (if it's unique, it is effectively an id) wherever needed.

The second one is the one that is being used often cause it does not require a central database or other authority to check uniqueness of ids, so scales better in distributed environments.

Drop the use of the common "id int" auto-incremented or not, and use UUIDs. Your id will be a varchar or a binary, an UUID implementation (like java.util.UUID, but you can find in other languages) will generate a unique id by itself whenever (and wherever, even on the client for example) you need it, and then you supply this id when saving.