Automatic insertion of the foreign key, when entering a product

[Paul Patrascu]

Hello,

My name is Paul and I am new in the programming.
Please help me to solve a problem, which seems simple at first glance, but which I cannot solve.
I have an application, on which I sell electrical and electronic products: fridges, washing machines, TV. I work in Java, with Intellij, Spring, controllers, repository and with cards for products.
I have two tables, one in which I enter products, named “products” and another with the categories of the entered products, named “categories”. The two tables are in “Many-to-one” relationship.
In the first table, I have the foreign key, named “category_id”.
I would like that when I enter a product in the “products” table, the foreign key, corresponding to the respective product, is also automatically inserted in the “products” table.
The entities for the two tables are:

@Entity
@Table(name="products")
public class ProductEntity {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer productId;
    private String name;
    private String brand;
    private Integer quantity;
    private String shortDescription;
    private String extraDescription;
    private Integer price;
    private String imageUrl;

    @ManyToOne
    @JoinColumn(name="categoryId")
    private CategoryEntity category;

//gettere and setters

@Entity
@Table(name="categories")
public class CategoryEntity {
    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    private Integer categoryId;
    private String categoryName;

    @OneToMany(mappedBy="category")
    private List<ProductEntity> products;

//getters and setters

In controllers, I have three methods, one for inserting and two methods for saving:

1)

@GetMapping("/product/add")
public ModelAndView addProduct() {
    ModelAndView modelAndView = new ModelAndView("productEdit");
    modelAndView.addObject("product", new ProductEntity());
    return modelAndView;
}

2)

@PostMapping(value = "/product/save", consumes = {"multipart/form-data"})
public ModelAndView productSave(@ModelAttribute("product") ProductEntity productEntity, @RequestParam("file") MultipartFile file) throws IOException {
    ModelAndView modelAndView = new ModelAndView("redirect:/productFirstPage");
    String path1 = "target/classes/static/imagines";
    String path2 = "src/main/resources/static/imagines";
    String filename = file.getOriginalFilename();
    if (filename != null && !filename.isEmpty()) {
        saveFile(path1, filename, file);
        saveFile(path2, filename, file);
        productEntity.setImageUrl("/imagines/" + filename);
    }
    productRepository.save(productEntity);
    return modelAndView;
}

3)

public void saveFile(String path, String fileName, MultipartFile file) throws IOException {
    Path uploadPath = Paths.get(path);
    if (!Files.exists(uploadPath)) {
        Files.createDirectories(uploadPath);
    }
    try (InputStream inputStream = file.getInputStream()) {
        Path filePath = uploadPath.resolve(fileName);
        Files.copy(inputStream, filePath, StandardCopyOption.REPLACE_EXISTING);
    } catch (IOException ex) {
        throw new IOException(ex.getMessage());
    }
}

I also have an html file, with a form for entering products, but I don't display it anymore.
I think that in the second method, I have to make some changes to solve the problem. I tried several options, but without success.
If you want more information, I will provide it to you immediately.
Can someone help me with a solution?
Thank you.

Paul

[Himai Minh]

I wonder if the automatic input for category ID should work.
In your product table,  you may need to remove the category name column and just put category ID as the foreign key.
This way, you can eliminate repetition.
I think as a developer who define the data ,you may need to define which category ID the product should have.

[Tim Holloway]

Liatura Vila edited this post to add code tags. As you can see, code tags make code a lot easier to read. Use the "Code" button on the message editor to create them.

My memory is hazy over details, but if it's not too wrong, the way to handle that is to save the foreign Entity and then obtain the key that was generated for it (and this is the part I REALLY don't remember — how to get that key). Once you do that, you can reference the keyed foreign Entity and save/update the referencing object.

This all should be done within a single Transaction.

[Liutauras Vilda]

I feel your product table's design is a bit off the track. Hence you are being forced into thinking about such complicated approaches.

1. You should not duplicate within the products table information already existing in the categories table.
product table has a field "name", which is actually what it seems to be a category_name within the categories table.

A simplified version of what I'd personally expect:

products (note, that "name" here is actually a product's name here, and not category name)

id	brand	name	category_id
1	Samsung	Viva 500	1

categories

id	name
1	Fridge

Note also, that an id field naming in categories table is consistent with products table's id field. No need to call it "category_id", also no need to call it "category_name", that should be name. You call category_id in products, only for the reason so it is clear which entity's id that is.

Now, when you say auto-populate category id (By entered name within product? What's the point?), I don't get that part. That ID supposed to come from some sort of web form, right? User should be able to select (and assign) a category for the product they are creating, i.e. selecting from a drop-down list or radio buttons. And those categories in the form would get shown by loading a list from categories table.

[Tim Holloway]

Liaturas has a point. Rather than use a generated key for the product, it would probably make more sense for the key to be a model number or SKU.

[Paul Patrascu]

Thank you for your information. I had a wrong approach. I will rebuilt the code.
Have a good day.

[Bear Bibeault]

Tim Holloway wrote:Liaturas has a point. Rather than use a generated key for the product, it would probably make more sense for the key to be a model number or SKU.

Strongly disagree. DB ids/keys should not be business info. They should just be ids (either a generated sequence number, or guid).

[Liutauras Vilda]

I didn't suggest to change the way keys are generated, indeed, I think they should be as they are now, auto incremented.

But product table should not have a category name, it should have just category id, because category name is already in categories table.

Having said that, categories shouldn't be created somehow through the product creation phase, but rather preselected during product creation time. If such category doesn't exist, that could be another screen to create a category first, so it could be used during product creation time.

If the above is adressed, the problem OP is raising wouldn't be a concern.

Foreign key constraints are exactly for that, so the products could get assigned the existing categories, rather than created those on the fly and get category id auto inserted to product <-- this kind of approach doesnt make sense for me, at least in this context (can't think of any other at the moment).

[Tim Holloway]

Bear Bibeault wrote:

Tim Holloway wrote:Liaturas has a point. Rather than use a generated key for the product, it would probably make more sense for the key to be a model number or SKU.

Strongly disagree. DB ids/keys should not be business info. They should just be ids (either a generated sequence number, or guid).

Why???      When the SKU or model number is what uniquely defines something, what benefit in adding an extra field that has no intrinsic meaning for the data? Security? I don't think so. Business logic isn't going to be looking up data via arbitrary ID, it's going to use the SKU/model number. Performance? I don't think so. If I had an arbitary ID and a product identifier code both as fields in the record, I'd be making BOTH of them defined as unique, so the overhead for adding a record is increased as well as the storage space needed for the extra value.

If you want to shard a database, it's best if you can do so on something meaningful. An auto-increment key is only meaningful in that it reflects how recently the record was added, and a UUID/GUID means nothing at all.

I have plenty of use for generated sequence numbers and UUIDs, but this isn't what I'd consider one of them.

[Bear Bibeault]

Today's "uniquely identifying business value" is tomorrow's migration headache.

[Tim Holloway]

Bear Bibeault wrote:Today's "uniquely identifying business value" is tomorrow's migration headache.

Nice maxim, but that's not generally something that applies to product IDs. For a lot of reasons, once a particular product is assigned an ID, it's going to be the same until no one ever needs replacement parts, technical manuals, or any sort of support.

New versions of a model may come out, but they will be assigned new model numbers. Even the "holiday special" versions of TVs at Wal-Mart have different IDs than their full-price counterparts.

Yes, there are other business values that are subject to change, but the only reason I have managed to assemble tech docs on a lot of the stuff years after purchase is that the model IDs they had engraved on them when I bought them still can (with luck) be searched on the Internet.

[Liutauras Vilda]

When I was writing I didnt mean this at that time, but I see Tim H went one step further, that's also could be considered as for product id..

However, the relation between product and category still I believe supposed to be as I described.

[Bear Bibeault]

Tim Holloway wrote:but that's not generally something that applies to product IDs.

The voice of experience says otherwise. I've been through three different migrations is separate organizations where such values used as database ids changed format and the migration was painful and costly.

There can also be security implications for exposing business values in URLs. (And yes, I once contracted for a place that had been using SSNs as database ids 😳);