auto increment field based of forgin key relation

I have a Category module that represents the category of a product in a store.
example category: ‘winter clothing’ and ‘hats’
I also have a Product module that has a name and a ForeignKey field to a category.
I want some kind of a running number in Product to show the order they inserted with the category.
I try to explain it the best I can but I know it’s hard to understand, so here is an example of what I want:

Categories:

id title
1 winter clothing
2 hats

Products:

id name category_id auto_number_in_category
1 cool hat1 2 (hats) 1 (first ref to hats)
2 cool hat2 2 (hats) 2 (secound ref to hats)
3 Coat 1 (winter clothing) 1 (first ref to winter clothing)

So my question is, how do I create this `auto_number_in_category`? Thank you very much!

Do these sequence numbers need to be in strict consecutive sequence, or do they just need to be in order?

If the later, you could just define that field as an autoincrement field, which might end up giving you something like this:

Notice that it does maintain the proper sequencing, even if the numbers aren’t consecutive. You could then derive the sequential numbers later when you retrieve them.

Note: There are other ways of doing this that would maintain the strict sequencing in the database, but they have particular intricacies of their own - particularly when you’re removing and/or reinserting entries. Trying to maintain strict consecutive order in that table is not a trivial issue.

Thank you for your response!
unfortunately, I need auto_number_in_category to be a strict consecutive sequence.
because I use it to generate a catalog number for the product.
How would I do that?

By the way, I saw I made a mistake in the products table, the id for Coat should have been 3 (fixed).
To clarify the id field in products and categories is the django’s default.

But why do you think the catalog numbers need to be in strict sequential sequence?

Also, are these catalog numbers stored somewhere else? Where else are these catalog numbers used?

Again, this raises the issue, what happens when a product is deleted? Do you then renumber the table? Do you reassign catalog numbers? If you don’t, then you end up with gaps in the sequencing anyway.

This is one of those types of items, that if I were given these requirements from someone else, I’d try to make it clear to them that this is not a trivial situation, and that the degree of effort may not be worth it for the desired results.
Let’s be real - who really cares what the catalog number is over time?

Now, having said that, if there were a truly valid reason why this might be considered a reasonable requirement, my immediate inclination would be to override the save method on the products class, such that when a row is saved, it checks to see if the number_in_category field is already set. If not, I’d do a query to find the currently-highest value for that category, add 1 to it, then assign it to that field.

the catalog number is very important to me, we later use it to give information and sort our warehouse.
the catalog number system we want to use is
ABB-000
A is category representation (to every category has a Char based on the order it added)
BB is a representation of the product inside the category (HOW DO I DO THIS?!)
000 is the products id
right now I’m focusing on the BB part of our catalog number.

Absolutely! No doubt about that. However, that doesn’t answer the questions:

  • Why do you think the catalog numbers need to be in strict sequential sequence?
  • What happens when a product is deleted?
    • Do you then renumber the table?
    • Do you reassign catalog numbers?
      (If not, then you end up with gaps in the catalog numbers anyway)
  • Are these catalog numbers stored somewhere else?
    • Are you building them once and then saving them, or are you dynamically creating them as needed in your application?

You can create a method in your model that returns data in a desired format. You can then call that method on an instance of that model to return your custom-formatted field. Typically, you’ll see this in various examples or tutorials as overriding the __str__ method on the class to allow functions like print(model_instance) to print something useful or informative.

Thank you very much for your help!