Alternatives to sql boolean datatype

Question

What are the situations where you would use a foreign key for a separate table, instead of using a logical one (for example, BIT in SQL Server)

For example, you would 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 */

eg. display_warning here may not display a warning for the driver, but display a warning to the mechanic who is testing the engine. Thus, a separate table would be appropriate.

is_test_sensor can be replaced with the parameter sensor_type (FK to sensor_types table), which has test types, live.

Answer 1

Np. Now I see what you get (I think).

, , , , FK . , ...

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);

, , , #,

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

, , . .

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
}

, , , .

, ;

• , , FK
• int ,
• --- --- . - ; 1) sql- - , engine_sensors , FK, , . .

, FK, , , .

, .

Answer 2

, . .

( YAGNI), .

Answer 3

, . 0 false 1 true. , - .

Answer 4

' SQL Server, , . , '

, , , .

, FK. , , .

, . (?) , ? ?

, / . .

Answer 5

engine_sensors

id ( ) fault_code

display_warning_engine_sensors/* */

id ( , FK - )

test_sensors/* */

id ( , FK - )

: , . , : , . , , relvar ( SQL: ).

"" , , " , [x hrs - y hrs].

Answer 6

. ( , , ) , ( ).

, "display_warning" (, ), . , ( - - ), , .

, ( , ), "" . , , , . - , :

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

Answer 7

- , . : t , , , , . , . , - . , .

Answer 8

-, . , .

, , , INT, BIGINT BINARY.

Answer 9

Using a "bit" in a SQL server (0 or 1) will automatically revert to logical in linq to SQL if you still want to have a boolean in your code.

But you did not indicate which database or why you want an alternative to bool.