On my user interface, I have to use a hard coded select option for a drop down and every time, that option is selected, I have to store a value corresponding to it in the database. I was thinking of storing negative numbers starting from -1 each time this option is selected and saved in the database. I plan on incrementing it by 1 so it will be -1, -2 , -3 and so on and so forth. Is there anything wrong with this approach?
If the value makes sense for the option they are selecting, there is nothing wrong with it. I do have to say though that I have never really needed to store negative numbers like that often. If the number represents an ID however, I suggest not using negative numbers for that.
But, if for instance, the value represented a negative coefficient for some kind of equation or something, it can be a valid value. Integer fields are just that, for storing integer numbers… positive or negative.
well in this case i think you meant incrementing by -1
and of course you’re using IDENTITY, right?
selected_option_id INTEGER IDENTITY(-1,-1)Correct.
So the column I am inserting into is an integer column and I am just planning to insert -1 the first time and then -2 and -3 etc.
I guess, I need to modify the design of the table for that particular column in SQL Server to something like selected_option_id INTEGER IDENTITY(-1,-1) ? Maybe I don’t need to modify because that selected_option_id column is being used for positive values as well. But I will have to keep checking the status everytime and keep on decrementing it based on that.
both positive and negative numbers?
why?
When the user selects any option other than the hard coded select option (let’s call it Other Option, I do have their actual positive values which are getting stored in the selected_option_id column.
When user selects only Other Option, then only I want to insert negative values.
Please let me know if you see anything wrong with this approach. I am not sure if I should consider having a separate column just for storing negative values in that case I could use what you suggested : selected_other_option_id INTEGER IDENTITY(-1,-1)
why?
edit: i’m just curious, that’s all
i honestly don’t know if you can insert negative numbers into an IDENTITY column without messing it up, so you might have to do away with IDENTITY and insert the positive numbers yourself too
my instincts would not allow me to do that, so i’m wondering why negative numbers are necessary
squints
What are you trying to do, exactly? Or perhaps more to the point… what are you trying to get OUT of the database?
I suspect a translation error from requirement to database design.
Maybe I will explain it diagrammatically. So I have a jQuery dialog like this:
The Pending Cases list is where I have a dropdown list which is dynamically generated. It has the id associated with each list item. These ids are what I am storing in the database whenever an item from this list is selected and saved in the database.
Now, I wanted to have an Other Case option. I could have hard coded it in the Pending Cases dropdown but I decided not to for the following reason.
There are some special cases user can enter in the Case Name text area. So I decided to have it as a checkbox along with the text saying special case - (Other Case and Special Case).
When, user selects the checkbox, I am disabling the dropdown like this:
Hence, now I need to decide what goes into the id field in the database when Other Case option is checked. Hence, I was thinking to insert -1 in the id column for that scenario.
So for the first time when user selects this checkbox, I will be inserting -1 in the column. User then closes the dialog and come back next day to enter any other Other Case and Specia Case. In this scenario, I was thinking of checking if there’s any negative value exists in the database and then decrement it by -1. So in my second scenario, and new record will be inserted with -2 value in the id column. So on an so forth.
Sure, please see my post(Using negative values in SQL Server - #9 by Jack_Tauson_Sr) above
So the table will have multiple rows that contain the same value (if it’s one of your dropdown options); if it’s not one of those options, you want a unique value.
CREATE TABLE caselog (
id INT NOT NULL AUTO_INCREMENT,
caseno INT NOT NULL DEFAULT -1,
casename VARCHAR
)
On entry of an “other” case:
INSERT INTO caselog(casename) VALUES (<thecasename>);
UPDATE caselog SET caseno = id WHERE caseno = -1
On entry of a dropdown case:
INSERT INTO caselog(caseno,casename) VALUES (<thecaseno>,<thecasename>)
(I’m sure you have other fields as well, but this is a for-example)
Basically, insert a default value entry into the table, and then update the table to set the row to the auto-generated ID. The “Other” cases wont be a consecutive number sequence, but they’ll be guaranteed to be unique. (You may need to pre-populate a couple of rows depending on the desired lock-in values of your dropdowns. You can also prepopulate row 0 and use that as your "other’ value, and make the caseno UNSIGNED, if you feel it necessary.)
you’re not going to want to use IDENTITY after all
Correct, Identity is not for this kind of thing. Here is one way you can do it using case_no column.
CREATE TABLE CaseLog (
Id int IDENTITY(1,1) PRIMARY KEY,
casename varchar(15),
case_no integer NOT NULL DEFAULT -1
);
DECLARE @maxCaseNo INT;
SET @maxCaseNo = (SELECT COALESCE(MAX(case_no), 0) FROM CaseLog);
INSERT INTO CaseLog(casename, case_no) VALUES ('Clark', @maxCaseNo - 1);
SET @maxCaseNo = @maxCaseNo - 1;
INSERT INTO CaseLog(casename, case_no) VALUES ('Dave', @maxCaseNo - 1);
SET @maxCaseNo = @maxCaseNo - 1;
INSERT INTO CaseLog(casename, case_no) VALUES ('Ava', @maxCaseNo - 1);
-- Inserting not special cases ...
INSERT INTO CaseLog(casename, case_no) VALUES ('Jonh', 0);
INSERT INTO CaseLog(casename, case_no) VALUES ('Kurt', 0);
INSERT INTO CaseLog(casename, case_no) VALUES ('Peter', 0);
-- fetch
SELECT * FROM CaseLog;
--fetch non special values only to populate dropdown ....
select * from CaseLog where case_no = 0
Here is the result of the 2 select statements:
Id casename case_no
----------- --------------- -----------
1 Clark -1
2 Dave -2
3 Ava -3
4 Jonh 0
5 Kurt 0
6 Peter 0
Id casename case_no
----------- --------------- -----------
4 Jonh 0
5 Kurt 0
6 Peter 0
Use the second select statement to populate your dropdown.
Why would you do max?
Surely if you’re going to try and finegal it, especially in a non-persistent environment like a web form processor, you’d take the MIN and then subtract 1 to get a false-identity?
I would prefer to store the case as a VARCHAR. What is the advantage to store it as int? You have only disadvantages. When you store the case by its name you directly can handle duplicates by ON DUPLICATE. When you debug your table in a SQL Manager you directly see what case it is and do not need to check, etc…
Depends on the use case, but id rather store a potentially 8 byte integer than the any-byte-length name of a case potentially millions of times over unnecessarily. The user indicated a desire or need to store multiple rows for the same case (perhaps i have misinterpreted that?) so an ON DUPLICATE clause wouldnt be appropriate?