What is the best data type for this entry? 1

[jgoodman00]

We have many fields, which are combo boxes. The user can select either "Y" or "N". At the moment, the fields are set to char(50).
Is this the best data type for storing this result?

I imported some tables from access & it converts them to nvarchar.
Would this be more suitable?
What field length should this be set to?

Also, if we have a field which can be either "BRB", "RAK" or "RSP" how long should this field be?

James Goodman
j.goodman00@btinternet.com

 

[balves]

Many people use the bit datatype for Y/N fields. A bit field can have the values 1, 0, or NULL.

If your code field is always completely filled (like U.S. state codes), then use char(3) or whatever the size of your code is. If your codes fluctuate in size, then use varchar or nvarchar.

 

[jgoodman00]

I wanted to use bit fields, but can they support the text entry of 'Y' or 'N' from the user?
When our database was access based, I set these fields to Yes/No datatype, but it would not allow the user to select 'Y' or 'N'... James Goodman
j.goodman00@btinternet.com

 

[moonshadow]

I agree with the BIT recommendation.
Additionally, if a field only has a limited number of values (like your "BRB", "RAK" or "RSP&quot, I would seriously consider a link to a reference (or lookup) table containing these values, and a key field (preferably identity field). You can then use an integer field in your main table which points to a value in your reference table, with a referential integrity constraint defined between the two tables. This will give you a number of advantages - you'll only get the values in your reference table (e.g. not "RRP&quot, you can offer the user a list of choices pulled from the table, you can add, change or delete the valid items from the list, and in most cases you will save space (possibly not in this one).
Note that NVARCHAR takes twice as much space as VARCHAR (2 bytes per character instead of 1), and is primarily for supporting languages with extended character sets e.g. Chinese. Microsoft prefers you to use these - but I suspect it has some deal with hard disk manufacturers.

 

[moonshadow]

With your BIT field, you can easily convert the values to/from "Y" or "N"
e.g. select substring('NY',bitfield + 1,1) from table
(the bitfield gets converted to an integer automatically I think)
and
set bitfield=CHARINDEX('Y', userinputvalue)
probably other ways of doing this, but this is the first I thought of - the CHARINDEX returns 1 if the userinputvalue is 'Y', or 0 otherwise (i.e. 'N')