sql boolean datatype alternatives

Question

What are the situations when you would use a foreign key to a separate table rather than use a boolean (i.e. BIT in SQL Server)

For example would you replace the following two booleans in this table:

engine_sensors
--------------
id (int, primary key)
name (varchar(50))
fault_code (int)
display_warning (boolean) /* if fault show driver a warning */
is_test_sensor (boolean) /* ignore this sensor in diagnostic checks */

e.g. display_warning here might not display the warning to a driver but do display a warning to a mechanic who is testing the engine. So a separate table would be appropriate.

is_test_sensor could be replaced by sensor_type (FK to sensor_types table) which has types of test,live.

Answer 1

This depends why you'd want to avoid it. You could just have number fields with 0 for false and 1 for true. Not sure if there are any benefits though.

Answer 2

If the fields model a boolean value, I would leave them as booleans. That's why they exist.

I would not attempt to future proof my database design (YAGNI principle), you can always change it at a later date.

Answer 3

First, I'd like to confirm Oded. If you need boolean values you should use the responsible column types.

However, if you have to store very much different boolean values it is sometimes more useful to use old-school bit-masks, stored in an INT, BIGINT or even BINARY column.

Greets Flo

Answer 4

Using 'bit' in SQL server (0 or 1) will automatically map back to a boolean in linq to SQL if you still wanted a boolean in your code.

But you haven't stated what database or why you want an alternative to bool.

Answer 5

You have added one piece of information

'Its is a SQL Server database and I understand that you'd use a bit field. I was wondering on how bad an idea it is to replace them with foreign keys to a separate table'

You should edit your original question and put this in if this is important to the solution you seek so that more people will be able to easily find it.

But you still haven't said why you were wondering about replacing them with a FK. If you tell people what your end goal is or what you are trying to achieve, they are more likely to provide a range of solutions.

I'm sorry I can't suggest a solution. Is a foreign key (to what?) better than a boolean value? compared to ?

I think you need to clarify / re-structure your question a bit. Good luck.

Answer 6

Np. Now I see what you are getting at (I think).

If you are asking what I think you are asking than yes, you might want to use a FK to a sensor table and list the sensors. This is typically what I would do...

CREATE TABLE [SensorType](
[Id] [int] NOT NULL,
[Type] [int] NOT NULL,
    [DisplayWarningTo] [int] NOT NULL,
[Description] [nvarchar](100) NULL,
CONSTRAINT [PK_SensorType_Id] PRIMARY KEY (Id),
    CONSTRAINT [FK_SensorType_WarningReceivor] FOREIGN KEY (DisplayWarningTo) REFERENCES WarningReceivor(Id)    
 );

CREATE TABLE [WarningReceiver](
[Id] [int] NOT NULL,
[Receiver] [int] NOT NULL,
CONSTRAINT [PK_WarningReceiver_Id] PRIMARY KEY (Id)
 );

------

INSERT INTO WarningReceiver(Id, Type) VALUES (1, 'Mechanic');
INSERT INTO WarningReceiver(Id, Type) VALUES (2, 'Driver');

INSERT INTO SensorType(Id, Type, DisplayWarningTo) VALUES (1, 'Rear sensor', 2);
INSERT INTO SensorType(Id, Type, DisplayWarningTo) VALUES (2, 'Test sensor', 1);
INSERT INTO SensorType(Id, Type, DisplayWarningTo) VALUES (3, 'Production sensor', 2);

I tend not to use identity columns on 'type' things like this and specify my own id which I map directly to a C# enumerated constant like

public enum SensorType
{
    RearSensor = 1,
    TestSensor = 2,
    ProductionSensor = 3
}

Then in your code when you pull out your engine sensor from the database you can just compare against your enum. e.g.

var engine_sensor = // get engine sensor from db.
if (engine_sensor == (int)SensorType.RearSensor)
{
   // do something
}
else if (engine_sensor == (int)SensorType.TestSensor)
{
   // display something to mechanic or whatever
}

I don't really know what your application domain is, so sorry if this doesn't make sense.

So to wrap up a couple of points and try and answer your question;

• Yes I do think you are better off with FK's
• You could just have them as int columns and define the sensors in code as I did with the enum
• I tend to do both --- define the enum for nice strong typing in code and --- create a foreign key table to complete my schema in the database. It's still worth having this for two reasons; 1) When writing sql queries in management studio or something and your looking at your engine_sensors table and see numbers for the sensor type you can join on your FK table to see what the sensors are. Makes things a bit easier

Lastly, if you have a FK table it enforces referential integrity and restricts the values you can put in as sensor types to what you have defined in the sensor type table.

Hope this helps somewhat.

Answer 7

engine_sensors

id (primary key) name fault_code

display_warning_engine_sensors /* if fault show driver a warning */

id (primary key, FK to engine_sensors)

test_sensors /* ignore this sensor in diagnostic checks */

id (primary key, FK to engine_sensors)

Remember : codes are poor, tables are rich. Regardless of how contradictory this seems : never use booleans to represent truth-valued information. The relational model already has a way for representing truth-valued information, and that is as the presence of some tuple in some relation that is the value of some relvar (in SQL terms : as the presence of some row in a table).

You cannot easily "extend" booleans to add extra functionality such as "display a warning only if the time is within the range [x hrs - y hrs].

Answer 8

Relational theory can help to answer the questions for you. WHERE the booleans go (in the table as shown or in a separate table as described in your example) should be determined by what the boolean data element is dependent (and fully dependent) on.

For example, if the data element "display_warning" depends only on the sensor (the entity your table is describing), then that column belongs in that table. However if it depends on other entities (the person - owner or mechanic - interacting with the sensor), then it more properly belongs in a different table where it can be fully and only dependent on the primary key of that table.

Ensuring that the data elements are dependent on the key (and nothing other than the key) is arrived at through "normalization" of the data. It's much to envolved to include in an answer here, but there are many references on the web that will help you to understand normalization more fully. Wikipedia is as good a place to start as any:

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

Answer 9

Indexing is one reason to avoid boolean types. You can;t index boolean fields, so if you have many records and search often on the fields that are boolean, having a separate table might be helpful. A separate sensor type table is also more extensible if you add more types. It is also helpful if there becomes a one to many relationships for a particular sensor type. Suppose an engine needed to have three of a particular type of sensor.