SQL Server 2008中的工作日数据类型

I have a list of manufacturers that delivers items on a designated day(s). For instance, manufactuer A might deliver items on Mondays and Thursdays. I need to design a page that displays this information. The page basically contains 7 checkboxes, monday through sunday that the manufacturers can toggle on/off to set their preferred day of delivery.

我有一份在指定日期交付产品的制造商清单。例如，制造商A可能在星期一和星期四交货。我需要设计一个页面来显示这些信息。页面基本上包含了7个复选框，从周一到周日，制造商可以选择打开/关闭来设置他们喜欢的交货日期。

The table I am creating in my SQL Server 2008 will contain a manufacturer ID, which is integer acting as primary key and a field to store the delivery day.

我在SQL Server 2008中创建的表将包含一个制造商ID，它是一个整数，充当主键，一个字段存储交付日。

What is the most appropriate datatype to use for the delivery days?

交付日使用的最合适的数据类型是什么?

2 个解决方案

#1

Assuming that a single day is sought;

假设要找一天;

I would choose TINYINT because;

我选择TINYINT是因为;

SELECT DATEPART(WEEKDAY, GETDATE())

will result in an integer ranging between 1-7 (incl)

将导致一个整数范围为1-7(包括)

Assuming multiple days are sought;

假设需要多日;

EDIT: I think an n:n solution might perform better but haven't confirmed.

编辑:我认为n:n的解决方案可能更好，但还没有得到确认。

Would you be interested in pursuing a solution like this? I chose this approach because I feel it is "neater" than having an n:n table. We can expand on it as you expand on your requirements.

你有兴趣追求这样的解决方案吗?我选择这种方法是因为我觉得它比n:n表格更“整洁”。当你扩展你的需求时，我们可以扩展它。

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.Manufacturer') IS NOT NULL DROP TABLE Manufacturer
IF OBJECT_ID('tempdb.dbo.DeliveryDays') IS NOT NULL DROP TABLE DeliveryDays
CREATE TABLE [dbo].[Manufacturer](
    [ManufacturerID] [int] IDENTITY(1,1) NOT NULL,
    [Manufacturer] [varchar](50) NOT NULL,
    [DeliveryDaysBinScore] [int] NULL,
 CONSTRAINT [PK_Manufacturer] PRIMARY KEY CLUSTERED 
(
    [ManufacturerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE DeliveryDays
(
     WeekdayNum INT
    ,tDayName   VARCHAR(9)
    ,DayBinVal  INT
)
INSERT INTO DeliveryDays VALUES 
(1, 'Sunday', 1),
(2, 'Monday', 2),
(3, 'Tuesday', 4),
(4, 'Wednesday', 8),
(5, 'Thursday', 16),
(6, 'Friday', 32),
(7, 'Saturday', 64)

/*
    Sunday      = 1 = 1
    Monday      = 2 = 2
    Tuesday     = 3 = 4
    Wednesday   = 4 = 8
    Thursday    = 5 = 16
    Friday      = 6 = 32
    Saturday    = 7 = 64
*/

INSERT INTO Manufacturer VALUES
('ABC Co', 16 + 1), ('Boxer PTY', 64+8), ('ConsumeIT', 1+4+16)

SELECT *
FROM Manufacturer   M
JOIN DeliveryDays   D ON M.DeliveryDaysBinScore & D.DayBinVal != 0

#2

With multiple delivery days:

与多个交付:

The best idea might be to have 7 BIT fields. This has advantages over storing as a single INT or VARCHAR in terms of indexing and the amount of records that needs to be scanned when querying on those fields.

最好的想法可能是有7位字段。在索引和查询这些字段时需要扫描的记录数量方面，这比将它们存储为一个INT或VARCHAR具有优势。

Just for completeness, I should note that MySQL has a variable length binary field (SQL-Server doesn't as far as I know).

为了完整性起见，我应该注意到MySQL有一个可变长度的二进制字段(据我所知，SQL-Server并没有这么长)。

The alternative is using a single INT field and using bitwise operations to determine which days are checked (or a VARCHAR if you want to be able to see the values easily when doing SELECT *, but you can always have a calculated field or stored proc for this).

另一种方法是使用一个INT字段，并使用位操作来确定要检查哪些天(如果您希望在执行SELECT *时能够轻松地看到值，可以使用VARCHAR，但是您始终可以拥有一个计算字段或为此存储的proc)。

With single delivery days:

单交货:

I would just recommend storing an INT that can have a value 0-6 or 1-7.

我只建议存储一个值为0-6或1-7的INT类型。

#1