-1

I have a rugby database + player table. In the player table I have performance and I want to represent the performance as

  • 0 = low
  • 1 = medium
  • 2 = high

I don't know what datatype the column should be. And what is the formula or function to do that?

Please help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Why do you want to store numbers instead of plain text? It can't be to save space; how many rugby players can you possibly be storing data for? – Bohemian Jan 29 '17 at 10:01

4 Answers4

5

You can define your column like this:

performance tinyint not null check (performance in (0, 1, 2))

tinyint takes only 1 byte for a value and values can range from 0 to 255.

If you store the values as 1 - Low, 2 - Medium, 3 - High and are using SQL server 2012+, then you can simply use CHOOSE function to convert the value to text when select like this:

select choose(performance,'Low','Medium','High')
. . .

If you really want to store as 0,1,2, use :

select choose(performance+1,'Low','Medium','High')
. . .

If you are using a lower version of SQL server, you can use CASE like this:

case performance 
    when 0 then 'Low'
    when 1 then 'Medium'
    when 2 then 'High'
end
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
1

There are a number of ways you can handle this. One way would be to represent the performance using an int column, which would take on values 0, 1, 2, .... To get the labels for those peformances, you could create a separate table which would map those numbers to descriptive strings, e.g.

id | text
0  | low
1  | medium
2  | high

You would then join to this table whenever you needed the full text description. Note that this is probably the only option which will scale as the number of performance types starts to get large.

If you don't want a separate table, you could also use a CASE expression to generate labels when querying, e.g.

CASE WHEN id = 0 THEN 'low'
     WHEN id = 1 THEN 'medium'
     WHEN id = 1 THEN 'high'
END
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

1- column datatype should b int. 2- where you send the date check the performance first like:- if(performance = low) perVar = 0 send into database

1

I would use a TINYINT datatype in the performance table to conserve space, then use a FOREIGN KEY CONSTRAINT from a second table which holds the descriptions. The constraint would force the entry of 0, 1, 2 in the performance table while providing a normalized solution that could grow to include additional perforamnce metrics.

swallace
  • 11
  • 1