| Author |
Topic |
|
rocknpop
Posting Yak Master
201 Posts |
 Posted - 2014-06-05 : 05:26:06
|
| Hi, suppose we have a master table with 2 columns: colorID INT PRIMARY KEY, colorName VARCHAR(50).Note that colorID is not Identity. Now the question is should we hard-code ID's in the application or should we hard-code the Names (colorName).One school of thought is since this is master data so ID's will never change and what if text changes, then we need to replace the text everywhere, thus use ID's. But I am not too sure of using ID's like this. I would prefer using text names.So what are the pros and cons for using either ID's or Names.Any thoughts on this?--------------------Rock n Roll with SQL |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-06-05 : 07:52:18
|
| In relational databases you would use the column colorID in other tables to be able to reference the colorName. That way if the name changes you do not need to change any other tables. As to hard coding the ID's in an application, see if there is a way to have a table give the values. This may mean adding columns to flag what is wanted when.djj |
 |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2014-06-05 : 08:08:07
|
| Hi, thanks for the reply.Yes I understand that in the relational DB we would use colorID as foreign key to reference colorName.In the application, if there is an inline query (or procedure called) such as 'Select someColumn FROM tblColors WHERE colorID=1'. My question is should we use colorID=1 (corresponds to colorName='Red') or should we use ' WHERE colorName = 'Red'. So I want say a specific column or set of columns where color is Red.--------------------Rock n Roll with SQL |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2014-06-05 : 08:11:57
|
In this case where is 'Red' coming from?Choosen by a user from a given list?If so, then you can display the colornames to the user to choose and take the (hidden) id for your select....
Too old to Rock'n'Roll too young to die. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2014-06-05 : 08:27:47
|
| colorID of Red=1 and this is hard-coded in the procedure itself. So the query is: SELECT table1.someColumn FROM table1 inner join tblColors ON colorid=table.colorId WHERE tblColors.colorID=1My question is about using colorID=1 or using colorName = 'Red'.--------------------Rock n Roll with SQL |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2014-06-05 : 08:40:58
|
Not sure what all this stuff means to you (hard-coded, application, procedure) in this context... The short answer is: colorID:)
Too old to Rock'n'Roll too young to die. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2014-06-05 : 09:19:31
|
| Ok, let me put it another way, out of the following queries which one would you suggest and why; why colorID=1 and why not colorName='Red' :SELECT table1.someColumn FROM table1 inner join tblColors ON colorid=table.colorId WHERE tblColors.colorID=1SELECT table1.someColumn FROM table1 inner join tblColors ON colorid=table.colorId WHERE tblColors.colorName='Red'--------------------Rock n Roll with SQL |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2014-06-05 : 09:30:44
|
A computer is "computing" and computing is faster with numbers than with text :)I guess, the table is indexed by the id column!
Too old to Rock'n'Roll too young to die. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2014-06-05 : 09:35:38
|
| I thought so, that was the only answer I could come up with myself too, but thanks for your reply.The reason I have been given is since it is master data so ID's will never change and text can change so better to use the ID; I was not convinced on this.--------------------Rock n Roll with SQL |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-05 : 11:22:49
|
It all depends and there is probably no real right answer. When it comes to applications (non-database) I like to use enumerations where it makes sense. When in a database it is probably slightly faster to just use the ID rather than join to another table to use a string, but it is much more user readable to see something = 'Red' then it is for something = 1. Another option if you are using stored procedures is to declare a "constant" and use that in place of a number. For example: DECLARE @Color_Red INT = 1;SELECT table1.someColumn FROM table1 WHERE tbl1.colorID = @Color_Red |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2014-06-06 : 01:40:50
|
| Thanks Lamprey, that makes sense, takes care of readability as well as integer match as against a string match.--------------------Rock n Roll with SQL |
|
|
|
|
To hard-code ID's or not?