How have you set up your database? Do you have to change a lot with a new year? [closed]

I have a question about databases. What does your database actually look like? Suppose you have a table for quotations.

For example, now it is 2021. You have 1000 (id AUTO_INCREMENT) requests at the end of the year, so 1000 rows.

How do you make the quotation numbering available for the year 2022 if they have to start from 0 again? Will you make a whole new table and change the codes everywhere on the website(as I do)? Or do you approach it completely differently?

Answer

I would add an entry date column and stamp each record as it is created. Then I would query the table for records that were created within a date window.

Example:

The below code will add a new column to your table.

By default this column will contain the date the record was entered.
(Any existing records will carry the date you run the query for the first time.)

ALTER TABLE [yourTable]
ADD [entryDate] [datetime] NOT NULL DEFAULT(GETDATE())

Afterward you can count records between two dates like this:

SELECT COUNT(*) AS [RECORD COUNT]
FROM [yourTable]
WHERE [entryDate] BETWEEN '2021-01-01' AND '2022-01-01'