Store days of week in a database

问题:

I have to do a small project for school. What is the best possibility to store days of the week in a database table?

E.g. days of the week for data backup — should I use a column like this:

backupDays varchar(50)
1,5       >> monday + friday
2,3,4     >>tuesday + wednesday + thursday

I have to use this table in my asp.net MVC program and I use a MSSQL database.

回答1:

In this case (7 boolean values in the one field) we get a binary set only 7 bits long. So you can use one byte length field type in MS SQL - tinyint. And use bitwise operators to manipulate it.

For example(binary):

00000001 -Sunday

00000011 - Monday and Sunday

00000101 - Tuesday and Sunday

00000111 - Monday,Tuesday and Sunday

Here you can find details and examples:

http://sqlfool.com/2009/02/bitwise-operations/

http://www.mssqltips.com/...

回答2:

In my opinion, the best way to store one day-of-week in a field, is to store it as a number (0..6). But it seems that you want to store more than one day in a field. Obviously, you can't store more than one value in a single field. You need to use a separate table:

create table Course
(
  CourseId int not null primary key,
  Name nvarchar(100) not null
);

create table CourseDayOfWeek
(
  CourseDayOfWeek int not null primary key,
  CourseId int not null references Course (CourseId),
  Day int not null
);

But if you really need to store multiple values in a single field (you can't afford creating a new table), you can use an xml field or a string field. Then you need to parse the field manually.