hardcoded ID vs query

Question

There's situation when I have table named "status", which only contains ID (PK) and name (string, unique). Also, other table has reference to this status (e.g. status_id)

let's say, there's two statuses:

1 - status1
2 - status2

Now, I'd like to insert/update record in table2 (which has reference to status table). What is the best way to do it, should I hardcode ID of status that I'd like to set or should I query by name, then get ID and assign it after?

NOTES: this is also general programming question (no direct SQL queries). I was not able to find a tag for it.

If you have the id, use the id. If you have the name, look up the id. — Gordon Linoff, Nov 15 '18 at 20:04
Well, I have them both. Question is, should I hardcode ID to insert/update or should I query by name, get ID and set afterwards? — Segfaultz, Nov 15 '18 at 20:06
If name is a unique column, I'd use name to get id instead of hardcoding it — Eray Balkanli, Nov 15 '18 at 20:07

score 0 · Accepted Answer · answered Nov 15 '18 at 20:12

If name is a column including unique values, you can use name field to get the ID and then use the ID. However, Usually the name column does not include unique values, so it is required to use other columns to receive only 1 id instead of multiple.

See the situation:

ID name
1  John White
2  John White

Here, if you use name field, you will get 2 different IDs returned which will cause an error. That's why you'll need another approach like:

..
where name = @name and dateOfBirth = @dob and MothersName=@mothersname

to make sure one unique id is being returned.

To sum up, if you are sure the name field includes unique values, use the field to get the ID instead of using the ID value hardcoded. Otherwise, you can try to create a key in the config file like "lookupid" and use its value instead of using the ID hardcoded still, will be better to maintain for the future.

makes sense, thanks! EDIT: I'll accept it in 3 minutes (limitation). — Segfaultz, Nov 15 '18 at 20:15

Himanshu · Answer 2 · 2018-11-15T21:10:56.980

0

Name isnt the best choice to check whats the id its the opposite because name at most of the times (can be same general case) would result into duplicates leading to redundancy of rows as you proceed with adding new columns as well making it worst. better choice as always is the primary key of the table or unique column you can have a unique constraint on your column to deal with duplicates in case you want it to be used as for a search for an id

edited Nov 15 '18 at 21:10

answered Nov 15 '18 at 20:57

Himanshu

3,830
2
10
29

edited question to include names are unique. I just didn't thought about that before, my bad. – Segfaultz Nov 15 '18 at 21:59

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

Hardcoding is never a good idea but depends on how often the value changes throughout the life of code...using the query is better

sql

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 15 '18 at 22:42

user3439907

11
2

hardcoded ID vs query

3 Answers3

sql