How to handle last generated value issue!

Databases
1

What’s the best way to keep track of last generated column value in the following scenario:

I have a name column in the BoxLocation table of MS SQL server(DDL shown below)
On the user interface, user has an option to select A or B or C from the dropdown list and based on this selection, I want to populate an HTML input text field as follows:

Scenario 1:

For the very first time, where there is nothing in the database table and user selects A from the dropdown, I want to populate A-1 in the name input HTML text field using javascript.

Scenario 2:

After first record has been inserted, the name fied in the table will containe A-1. So I if user selects A from the dropdown again, I want to populate A-2 this time in the input HTML
field.

Similary, user can do after selecting B or C from the list so I will have to display B-1(if it’s first time) or B-2 (for second time) of if C is selected,
then C-1(if it’s first time) or C-2 (for second time)

Should I consider adding a field in the database table like lastUsed and get the next possible value based on the user’s selection? or what’s the best approach to achieve the same?

Here’s the DDL:

USE [boxdatabase]
GO
/****** Object:  Table [dbo].[BoxLocation]    Script Date: 7/13/2023 10:47:19 AM ******/
SET ANSI_NULLS ON
GO
    
SET QUOTED_IDENTIFIER ON
GO
    
CREATE TABLE [dbo].[BoxLocation](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [description] [varchar](250) NOT NULL,
    [comment] [varchar](250) NULL,
    [locationId] [int] NULL,
    [capacity] [int] NULL,
    [isFull] [bit] NULL,
    [entryDate] [datetime] NULL,
    [endDate] [datetime] NULL,
CONSTRAINT [PK_BoxLocation] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BoxLocation] ADD  CONSTRAINT [DF_BoxLocation_entryDate]  DEFAULT (getdate()) FOR [entryDate]
GO
2

When the user selects the second and subsequent options in the dropdown, where are the entered values being stored in the database?

3

So what happens if the user selects A second time. You have A-2 in the database. Now the user selects B. Is it now B-1? What is if he selects A after B? Is it them A-1 again or should it be A-3?

4

I have the biggest urge to shout “You sank my battleship” at this point.

anyway.

You’ve already got all of the information you need to generate the ‘index’ of the box, the question mostly is how often you’d be doing so, as to whether its better to store the information in the table or generate it on-the-fly.

For example.

3 entries come into your table, as is. A B A.

I’m… going to do this in SQL, because I dont really know the database you’re using, but the point should be clear.

I store A, B, A as… the names.

You want to put into your text field the next value for A.

SELECT COUNT(name) + 1 FROM BoxLocation WHERE name = "A" - yields 3. I put A-3 in the box.

Now, this is a little bit of effort on the database engine’s part. You COULD store “A-1”,“A-2” in the database, query it for the latest entry:
SELECT name FROM BoxLocation WHERE name LIKE "A-%" ORDER BY entryDate DESC LIMIT 1
and then use your receiving language to determine the next value based on the value returned.

What you store may be more relevant to you depending on what your output other than filling this box is; for example, if you want to generate a report from the data, you might want to lean towards storing A-1, A-2 etc in the database for sake of not having to generate the numbers again.

5

Storing 2 information in one column is mostly a wrong design

2 Likes
6

When user selects A second time, since there is A-1 in the table, I am expecting to populate A-2 in the HTML input. A-2 will not be in the database until A-1 is there based on the flow of events I’m trying to follow.

Yes, it will be B-1 since this is first time user has selected B and there is not entry in the table.

It will be A-3.

Also, I have A, B and C stored in a separate table of MS SQL Server 2012 database so I’m getting A,B and C from there - forgot to mention this. Please let me know if I can answer any questions.

7

If user selects A first time, A-1 is expected to populate and A-1 isn’t stored anywhere yet. It’s only stored once user has saved the form info.

I was wondering if I should create a sequence for A,B and C and probaby other things like D,E,F etc and then get the next sequentia number based on this? Wondering if creating 20-30 sequences or even more is really bad idea.

8

I’m using MS SQL Server 2012. Do you think using sequenc is a good idea? So I will just create a sequence like this CREATE SEQUENCE A START WITH 1 INCREMENT BY 1; and similarly for B and C . So this will only give me a numerical numbers I believe back after I call NEXT VALUE FOR for each of A , B and C . So I will have to append it on the user interface with A- and then save it to the name column like A-1 . Does this sound right? But since my content is not imited to 3 characters like A,B and C and it can be many more so creating a sequence could be overhead ?

Also, there is a separate form in the UI where user is creating A,B, and C and that goes in different table so if sequence is not an overhead then I’m wondering if I can create a sequence as soon as somone created A or B or C or D etc ?

9

I am thinking of proceeding in this manner by adding a lastUsedValue column to the table. For example, I created a test database and a table in MySQL just to play with SQL and it looks like the following:

image

While inserting next BOX in the boxName column, I could use this query to get the latest value of lastUsedValue column

select lastUsedValue from sampleboxlocation
WHERE boxName LIKE 'BOX%'
ORDER BY ID DESC
LIMIT 1;

Above query gives me 3.

This gives me the last used value for BOX. So while inserting another boxName , I can use the lastUsedValue + 1 (3+1) to make the new name BOX-4 and populate in the HTML field , which will eventually get saved in the table.

10

Why do you need the name to contain the number? Just call it DEX. If you need the number do a

SELECT CONCAT(boxName, '-',lastUsedValue) AS boxName

Do not store two values in one column and do not store redundant data in a column.
if you need the combination of name and number being unique, then add a combined primary key or unique index on the fields.

But do not save the same data in multiple columns.

11

Because it indicates a different type of location. So BOX-1 is one location, and DEX-1 is another. Any alternate suggestion (other than using lastUsedValue then) since it’s like storing redundant data in a column in my case? I have unique index on boxName column and id is a primary key

12

You don’t need to store the name and number in a single column. Put them in individual columns and combine them when you query the data for display. Then you can get the next highest number by just doing

select max(boxIndex)+1 from sampleboxlocation where boxName='BOX'
13

But eventually, the combination is going to get stored in the boxName column as a string. Please let me know if I there are nay questions. Thanks!

14

Are you not able to change the schema to use separate columns? Is there some requirement that the combined values be stored in a single column, rather than as separate columns you combine later?

The proper way to do this is store the data separately and either combine them in your select query, or use a computed column to combine them.

1 Like
15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.