| Author |
Topic |
|
mridang_agarwal
Starting Member
22 Posts |
 Posted - 2006-09-28 : 02:54:33
|
| Hey guys,I have a colum in one of my tables called "Grade". Now this column can can contain the values:1. BOP2. OF3. PD4. BOP(SM)5. D(1)6. PDWhat should i set the datatype of he column as? Should i keep it as varchar (7) or should i keep it as an int field. The Grade column can contain a value from the above mentioned list. The list will nver change. Database size is not an deciding factor in my case beause the number of records are quite less. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-28 : 02:57:33
|
| Create a new table named Grades.The above options should be inserted. Make GradeID primary key and GradeName ordinary VARCHAR(7).Alter your other tables to reference this new Grade table and change column name from Grade to GradeID.Even if it seems unnecessary, it is good practice to keep normalization. No one knows what will happen to the system in the future. You maybe are not longer working for them, and a new developer takes over your code.Peter LarssonHelsingborg, Sweden |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-28 : 04:09:34
|
| I vote for job security.Grade varchar(7) not null check Grade in ('BOP','OF','PD','BOP(SM)','D(1)','PD')DavidMProduction is just another testing cycle |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-28 : 04:13:39
|
quote:
Originally posted by mridang_agarwal
The list will never change.
Trust me. It will change some time in the future. It may not be your choice, rather than a new manager.Peter LarssonHelsingborg, Sweden |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-28 : 04:17:23
|
quote:
Trust me. It will change some time in the future
I vote for job security.DavidMProduction is just another testing cycle |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-28 : 04:38:48
|
| I am very interested in your opinion of job security.Please explain your opinion of creating a check constraint rather than foreign key.For example, how do you distinguish between3. PD6. PDPeter LarssonHelsingborg, Sweden |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-28 : 05:52:21
|
quote:
For example, how do you distinguish between3. PD6. PD
You don't. It's a duplicate.DavidMProduction is just another testing cycle |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-28 : 05:59:19
|
| Ok, let's suppose those two are duplicates.I am still interested in reading why you think a check constraint would be a better alternative than a foreign key.The possibility that you know something I don't is present. I am willing to learn something new every day.Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-28 : 06:09:11
|
peso: Its that Write-Only Code thing again - no one else will understand it, so your job is safe!!mridang_agarwal: If there is a chance that the "labels" for these might change in the future, or if you would actually prefer to have "more descriptive" labels then go with the INT and JOIN it to a Lookup Table.However, if you use an INT and are then looking at the data in the "Grade" column of your table it might be a bit hard to be able to quickly know that 1=BOP etc. - so using a user-allocated varchar(7) key of "BOP" may make reading the data easier (i.e. Join the varchar(7) code to the lookup table, or use a check constraint).There-again, if there is a chance that "BOP" will need to change to "XYZ" in the future, then you will wish that you had put an INT in there linking to a Lookup Table  - Ah, I'm going recursive already, so I'll stop now!Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-28 : 06:59:54
|
Aaahh!Now I got an explanation of "write-only" code...  Peter LarssonHelsingborg, Sweden |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-09-28 : 07:20:35
|
quote:
I am still interested in reading why you think a check constraint would be a better alternative than a foreign key.
Your foreign key method requires an extra table with 2 candidate keys and a foreign key constraint plus the column itself.The check constraint on the other hand is just one constraint.DavidMProduction is just another testing cycle |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-09-28 : 08:22:25
|
| As always, I am willing to bet that those grades have other attributes that will be worth storing. i.e., a description, or some business rule attributes that pertain to each, and so on. If so, they should be in a separate, related table. see: http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx , example #4.I tend to put as much information into my database as possible to describe all entities, it makes things much easier to maintain and to code, and your data is very self-documenting. While I respect David's knowledge very much, that is one place where we disagree -- I would generally not list out specific values for a column in a constraint, but that's just my opinion of course.Looking at the list of values provided by the poster, it looks like there might be two pieces of info stuffed into one column for some of these and/or some presentation formatted into the data (i.e., what does the stuff in parens mean? Should it be a separate column?). If so, I would break them out into multiple columns to normalize this. I assume the duplicate is a typo, if not, then more info is definitely needed.Finally, I would not add an identity as a primary key, I'd use a natural key for this table since there seems to be a nice, short varchar() "code" for each that should work well.- Jeff |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-09-28 : 09:05:50
|
| How bout a 6 bit bitmask with a constraint to ensure only one on bit at a time?!? (hehe)I've used all three (I'm not including mine as real) suggestions in this thread and I kinda believe there is a time and place for each. For example, if Grade is a type 2 slowly changing dimension, most people would use the surrogate Peso suggests. However, if Grade is some attribute in a high-transaction system with little/no reporting needs based on grade, I would use the check constraint. mridang_agarwal needs to either give us more detail about how this Grade data is used or take all three into consideration on his own, consider, test and make a decision. (I would encourcage the poster to modify Peso's suggestion and use a smaller datatype for the surrogate ... tinyint perhaps.)A good poster would take all this into consideration, make a choice and then post back on the reasons for/against each solution based on his problem domain and tell us why he made the choice he made. That could open up a much more interesting discussion.Jay White |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-28 : 09:24:02
|
>> A good poster would take all this into consideration, make a choice and then post back on the reasons for/against each solution based on his problem domain and tell us why he made the choice he made. So, you think this is a homework question?  Kristen |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-09-28 : 09:42:38
|
| No, my homework radar didn't go off on this one. I'm suggesting that a) the answer to the posters question is really "It depends" (as is the answer to most sql questions) and b) given the advice, the poster could make an informed (read: tested, logic-proven) choice and use that to springboard a discussion about the merits of each approach given a particular problem domain."How do I model an boss-subordinate relationship?" ... boring!"Thanks for your input guys, I decided to implement my acyclic reconvergent directed graph using the transitive closure model Page47 suggest because x,y,z factors in my problem domain. Is my reasoning sound or are there factors I've missed?" ... interesting!Jay White |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-09-28 : 09:43:37
|
quote:
Originally posted by Page47A good poster would take all this into consideration, make a choice and then post back on the reasons for/against each solution based on his problem domain and tell us why he made the choice he made. That could open up a much more interesting discussion.Jay White
Jay, you've been away too long ... most "discussions" these days consists of about 20-30 posts trying to convince someone to stop using VARCHARs to store dates ..... - Jeff |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-09-28 : 10:05:56
|
quote:
Originally posted by jsmith8858Jay, you've been away too long ... most "discussions" these days consists of about 20-30 posts trying to convince someone to stop using VARCHARs to store dates .....- Jeff
Certainly more thoughtful discussion are not prohibited, right?Jay White |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-09-28 : 10:24:01
|
quote:
Originally posted by Page47
quote:
Originally posted by jsmith8858Jay, you've been away too long ... most "discussions" these days consists of about 20-30 posts trying to convince someone to stop using VARCHARs to store dates .....- Jeff
Certainly more thoughtful discussion are not prohibited, right?Jay White
Absolutely not !!! - Jeff |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-28 : 11:41:07
|
| I would go with Peters suggestion on this on. That data has the look of something that will change, probably by next week. Boss man, "We need to be able to show the grades in Spanish or French now."Another approach to this is to consider the following question: "What will I have to do if the values change?" With Peters solution, you change values in one column of a single table. If you are using a natural key, you have to update the constraints and maybe the data types, and then update the values in every table where it occurs.So in the end, you've got to ask yourself one question: "Do I feel lucky?" Well, do ya? (Apologies to Harry Callahan)CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-28 : 13:35:53
|
| "We need to be able to show the grades in Spanish or French now."Great spec!Boss man: Why aren't the translations to the grades done?You: I decided to do the Spanish translation, and next week.Kristen |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-09-28 : 13:40:53
|
| So, you contend that the benefits of the convience of surrogate keys outweights the opportunity costs of data integrity and performance? I think that is debateable. I think you can write your troublesome natural key update scripts without too much difficulty, considering their expected frequency. I might even contend that over the coarse of the life a database you may spend 80 hours typing "inner join lookuptable on maintable.someid = lookuptable.someid" over and over... both yours and mine are frankly silly positions in the nat/sur key debate.I'm cool with surrogates when you are doing dimensional modeling and are taking advantage of shared dimensions, but datawarehouses scratch a different itch than highly-transactional systems and an "attribute" is something very different than a "dimension" ... well, usually.The bottom line is the none on this thread knows anything about mridang_agarwal's database, so none of us can really suggest much of anything.Jay White |
|
|
|
Next Page
|
advice of field data type