Sql-server – Using Text Fields As Booleans

application-designdatabase-designsql server

We are developing a product system and I was curious to what industry standards were for many Boolean statements.

In our system we have some, what you could call main, bit fields. E.G. isBackordered, isDiscontinued.

We also have a lot of internal processes and debated on using product tags to denote if this product should be processed that way or not. E.G. isManufacturable, isLaserable

I was wondering if it is better in the industry to just create everything as bit fields or acceptable in the industry to use tags for special circumstances like that.

Best Answer

If you have a system with a large number of tags it's probably more efficient management-wise to use a relational system between products and product-tags.

i.e.:

IF OBJECT_ID(N'dbo.ProductsTags', N'U') IS NOT NULL
DROP TABLE dbo.ProductsTags;
IF OBJECT_ID(N'dbo.Products', N'U') IS NOT NULL
DROP TABLE dbo.Products;
IF OBJECT_ID(N'dbo.Tags', N'U') IS NOT NULL
DROP TABLE dbo.Tags;
CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName varchar(100) NOT NULL
);

CREATE TABLE dbo.Tags
(
    TagID int NOT NULL
        CONSTRAINT PK_Tags
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , TagName varchar(100) NOT NULL
);

CREATE TABLE dbo.ProductsTags
(
    ProductID int NOT NULL
        CONSTRAINT FK_ProductsTags_ProductID
        FOREIGN KEY 
        REFERENCES dbo.Products(ProductID)
    , TagID int NOT NULL
        CONSTRAINT FK_ProductsTags_TagID
        FOREIGN KEY
        REFERENCES dbo.Tags(TagID)
    , CONSTRAINT PK_ProductsTags
        PRIMARY KEY CLUSTERED
        (ProductID, TagID)
);

Insert some data into the various tables:

INSERT INTO dbo.Products (ProductName)
VALUES ('wallet')
    , ('Paint');

INSERT INTO dbo.Tags (TagName)
VALUES ('IsLeather')
    , ('IsPlastic')
    , ('IsBlue');

INSERT INTO dbo.ProductsTags(ProductID, TagID)
VALUES (1, 1)
    , (2, 2)
    , (2, 3);

Query the tables:

SELECT p.ProductName
    , t.TagName
FROM dbo.ProductsTags pt
    INNER JOIN dbo.Products p ON pt.ProductID = p.ProductID
    INNER JOIN dbo.Tags t ON pt.TagID = t.TagID

╔═════════════╦═══════════╗
║ ProductName ║  TagName  ║
╠═════════════╬═══════════╣
║ wallet      ║ IsLeather ║
║ Paint       ║ IsPlastic ║
║ Paint       ║ IsBlue    ║
╚═════════════╩═══════════╝

This allows you to extend the tags at will without needing to redesign the database itself.

Using discrete bit columns in the products table is a strategy that will soon get very tedious. Take for example:

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName varchar(100) NOT NULL
    , IsLeather bit NOT NULL
    , IsPlastic bit NOT NULL
    , IsBlue bit NOT NULL
);

SELECT p.ProductID
    , p.ProductName
    , p.IsLeather
    , p.IsPlastic
    , p.IsBlue
FROM dbo.Products p

INSERT INTO dbo.Products (ProductName, IsLeather, IsPlastic, IsBlue)
VALUES ('Wallet', 1, 0, 1);

Now, if you want to add products that might be red, you must alter the table via:

ALTER TABLE dbo.Products
ADD IsRed bit NOT NULL;

You must also modify any code that touches the dbo.Products table, to make that code aware of the new IsRed column.

Related Question