Clever Geek Handbook

Create empty objects in db or save them later

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, the real table is much more complicated, but that's enough to understand the problem.

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

I see two possible options (both ugly, really):

  • Show a modal list with product models for the user and then create an object in the database using the productModelId selected by the user.

  • To create a temporary number and then save the object to db when the user finishes editing the document and saves the ID. We also need to remove the NOT NULL case and check this code in the code.

The first way is bad because we have too many modals in our application. Our user interface is too heavy with them.

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

What can you offer us? Any new solutions? What are you doing in your applications? Maybe some UI hints. We are currently using the first option.

+3


source to share


2 answers


The theory says that the id you are using in your database should not be relevant information, so the user should not see it unless it is hidden in a url or similar, so you should not display it to the user, and the problem is with you have one possible confirmation of this theory.

Currently the solution you have is partially correct: it satisfies the technical requirements, but still bad, because if the user does not complete the insert, you will end up with a DB having empty records (which means id and external key ok, but that's all the rest of the fields are empty or useless defaults), so you basically bypass database checks.

There are two best solutions, but this requires an overview of your database.

First, don't use the ID as something to display to the user. Use a different column with a different "id", declare it unique in the database, generate it in the application, render it to the user, and then use that different "id" (if unique, this is indeed an identifier) ​​where appropriate.



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

Ditch using generic "id int" with auto-appending or not and use UUID. Your ID will be varchar or binary, the UUID implementation (like java.util.UUID, but you can find in other languages) will generate a unique ID on its own whenever (and wherever you are, like on the client) it and then you will specify that id when saving.

+2


source


We do this in the following way.

Created an id_requests table with issue_type_id and lastId fields. We need this to avoid a situation where two users click the "new" button and get the same IDs.



And of course, we added the innerNum field to all tables where we use this function.

Thank!

0


source






More articles:


All Articles