How do you deal with char(1) in place of a boolean and tri-state fields?

Question

Somewhat related to my question about integers instead of decimals; my vendor provides a lot of "boolean" fields in the char(1) format (i.e. Y/N). Some of these fields legitimately cannot be a boolean because they can have several values, but the majority can be treated as a boolean. In my previous question the advice was to do what's "right" instead of what the vendor provides; should I still apply this logic and create my database schema using a bit field for these columns, or keep it as a char(1) and do the conversion in my code?

Also on this subject, how should I deal with the tri-state fields as far as code goes? Logically the field is a boolean (in the sense that I'm only interested in the Y/N value and the third value is really either yes or no), but the values can be more than just true/false (e.g. there is a UpsShippable field that can be Y, N or G); this field has multiple states so how would I best encapsulate it? Something like an enum (or static constants, since Enums can't be used with the char datatype)? In the multi-value cases the data is more like a type indicator than a flag.

To sum up (I get a little long-winded): 1) When dealing with char(1) values in data, would you keep them as chars or convert to bit (or whatever your database's boolean type is) and why, and 2) How would you tackle a tri-state char field in your code, assuming you would leave it as char(1) in the data schema?

EDIT: To clarify, none of these fields are used for "real" logic, it's basically just an indicator. For example, if the item isn't shippable via UPS (i.e. the value is N/G) then on the customer-facing page it says the item cannot be shipped via UPS, and on the back-end the logic won't make a call to UPS's web service to calculate shipping. The other Y/N fields are simply there as extra detail about an item and have no logic, although they need to be changeable (for example, have a checkbox indicating if it's recycled on a data entry form on the back end); I might display an image or filter items by them (e.g. you can search for all recycled products, and I'll check to make sure their recycled indicator is true) but nothing else, at least not at this point in time.

Answer 1

I would suggest in the bistate fields, you always make the boolean conversions.

The tri-state field is something different... There's many solutions, none definitely "right". You could create tables for the optionsets, and link by index...but you lose in human readability for very little gain except being "right".

Answer 2

I most often deal with Oracle which doesn't really have bit types so its never really been an issue.

That being said, one character code fields are common and fine. Whatever you do though don't give them a misleading name that suggests it's a boolean type if it had 3 states. That's just going to confuse people.

Bad name: SHIP_FLAG ('flag' is ambiguous but many will interpret it as Y/N or T/F)
Bad name: HAS_BOOKED (again, implies boolean)
Bad name: IS_SENT (ditto)
Good name: SHIP_CODE (this can mean anything you want it to mean)

Also, one character fields allow you to extend the meaning later. Bit fields don't (really).

Answer 3

1:) Depends if the char(1) is only a Y/N T/F type field then yes I would convert it to a bit, cause it is a boolean, I would do the conversion to the vendor specific format at the edge of the system (Imagine you have multiple vendors like fedEx and UPS you might be able to generalize your backend system to handle both shippers then plugin specific components to communicate btw your model and theirs).

2:) If there are multiple states then this is no longer a boolean and I would store it in a format that made sense some options would be to store it as a char field, or you could create a lookup table so for UpsShippable you might have Yes, No, or Ground. Well a lookup table allows you to store a more descriptive info about what G means. If I would use an Enum in the code depends a lot on what the purpose of the field and your project is. If your going to run logic against the field then I might look at using an enum which goes nicely with a lookup table, if your just storing and passing the data then perhaps I wouldn't.

Answer 4

I've decided I'm going to go ahead and implement the simple Y/N fields as true bit fields, but I'm still not sure how I should tackle the multi-state char fields.

I will need to use them as flags to indicate if other actions should be taken; for example, unless the ShippingStatusCode is Y, the item is considered to not be UPS shippable and a different set of logic applies than if it was shippable. The logic can range from not displaying certain options to actually replacing items that have been entered into a form with a totally different item, or removing it completely if a lookup indicates there are no equivalent items.

One problem down.. one to go. Thoughts on dealing with the multi-state fields? I have to leave them as char fields, although I think I'm going to err on the side of caution and use a char(3) instead of char(1) just in case requirements do change at some point.

Answer 5

I ran into a similar situation a while ago. I was building an app against a database I had no control over, which used char(1) Y/N fields for booleans. I was also using the SubSonic ORM framework to build my data access layer. What I did was to create another property on the class for a table to convert to/from the char(1) field.

    public string SomeYNField { ... } // property generated by ORM tool

    // property added manually (in a partial class, so 
    // it doesnt get blown away by the ORM tool)
    public bool SomeYNFieldBool
    {

        get 
        { 
            // Anything other than a "Y" is false.
            return SomeYNField.Equals("Y", StringComparison.InvariantCultureIgnoreCase); 
        }
        set 
        {
            SomeYNField = value ? "Y" : "N"; 
        }
    }

Now you can use that boolean field to bind to checkboxes and other controls that are expecting booleans.

Answer 6

You can create a view that cases the tri-state boolean fields into a NULLable bit field.

For example:

CASE UpsShippable WHEN 'Y' THEN 1 WHEN 'N' THEN 0 ELSE NULL END

If you create this view as an indexed view, there will be no overhead when SELECTing from the data.