MSSQL查看他们可能会帮助的一些时间问题。 [英] MSSQL Views a tine problem they may help with.
问题描述
我有一张桌子：学校，
和另一张桌子：教师
作为一名教师可能被分配到多个学校我将这些与
第三张表：SchoolTeachers
我索引（唯一）这个关于SchoolID和TeacherID的链接表以防止
将一名教师分配给一所学校更多不过一次。
到目前为止，这么好。
但是学校管理员很难理解可能会有
不止一个Lynda Jones，老师，被分配到一所学校，并且
数据库将用他们独特的TeacherID跟踪这些人，
打印出多个Lynda Jones的时间表可能会混淆
学生。
所以我想阻止任何一所学校有一个以上的Lynda Jones，
要求任何学校都有独特的教师姓名。
如何做到这一点？
我目前的解决方案 - >索引视图：
Sub CreateSQLObject（）
使用CurrentProject.Connection
.Execute 创建视图dbo.SchoolsandTeachers with SCHEMABINDING AS
SELECT st.SchoolTeacherID，s.SchoolID，s.SchoolName，t.TeacherID，
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeachers st ON s.SchoolID =
st.SchoolID JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID"
.Execute SET ARITHABORT ON
。执行创建独特的集群索引ixSchoolsTeachers ON
dbo.SchoolsandTeachers（SchoolTeacherID）"
。执行创建独特的指数ixSchoolsandTeachers ON
dbo.SchoolsandTeachers（SchoolID，TeacherName）"
结束
End Sub
当然，这还没有在战斗中尝试过。但它似乎很有希望。
这些不会是巨大的表，所以我希望索引开销将是最小的b $ b。欢迎提出意见。
-
Lyle
（电子邮件参考 http://ffdba.com/ ）
I have a table: Schools,
and another table: Teachers
as one teacher may be assigned to more than one school I link these with a
third table: SchoolTeachers
I index (unique) this linking table on SchoolID and TeacherID to prevent
assigning a teacher to a school more than once.
So far, so good.
But School Administrators have difficulty with the idea that there might be
more than one "Lynda Jones", teacher, assigned to one school, and that the
database will keep track of these individuals with their unique TeacherID,
and timetables printed out for more than one Lynda Jones may confuse
students.
So I want to prevent any school from having more than one Lynda Jones, that
is require unique teacher names for any school.
How to do this?
My current solution -> an indexed view:
Sub CreateSQLObject()
With CurrentProject.Connection
.Execute "CREATE VIEW dbo.SchoolsandTeachers WITH SCHEMABINDING AS
SELECT st.SchoolTeacherID, s.SchoolID, s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeachers st ON s.SchoolID =
st.SchoolID JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID"
.Execute "SET ARITHABORT ON"
.Execute "CREATE UNIQUE CLUSTERED INDEX ixSchoolsTeachers ON
dbo.SchoolsandTeachers (SchoolTeacherID)"
.Execute "CREATE UNIQUE INDEX ixSchoolsandTeachers ON
dbo.SchoolsandTeachers (SchoolID, TeacherName)"
End With
End Sub
Of course, this has not yet been tried in battle. But it seems promising.
These will not be huge tables so I am hoping the indexing overhead will be
minimal. Comments are welcome.
--
Lyle
(for e-mail refer to http://ffdba.com/)
推荐答案
Lyle Fairfield< Mi ************ @ Invalid.Com>写在
新闻：Xn ******************* @ 130.133.1.4：
任何人知道如何拼写检查主题吗？
-
Lyle
（电子邮件参考 http://ffdba.com/ ）
Lyle Fairfield <Mi************@Invalid.Com> wrote in
news:Xn*******************@130.133.1.4:
Anyone know how to spellcheck Subjects?
--
Lyle
(for e-mail refer to http://ffdba.com/)
没有冒犯，莱尔，但这是一个现实的限制：防止一所学校
有2名同名教师？在12人的部门，我们有两个人的名字是
（他们的妻子的名字和
一样好！）
-
Doug Steele，Microsoft Access MVP
http ：//I.Am/DougSteele
（请不要发电子邮件！）
" Lyle Fairfield" <弥************ @ Invalid.Com>在消息中写道
新闻：Xn ******************* @ 130.133.1.4 ...
No offence, Lyle, but is that a realistic limitation: to prevent a school
from having 2 teachers with the same name? We had two individuals with the
same name in a 12 person department (and their wives had the same names as
well!)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
我有一张桌子：学校，
和另一张桌子：教师
作为一名教师可能被分配到一所以上的学校我将这些与第三张桌子联系起来：学校老师
我在SchoolID和TeacherID上索引（唯一）这个链接表，以防止将教师分配到学校不止一次。
到目前为止，这么好。
但是学校管理员很难想到可能会有超过一个Lynda Jones，老师，被分配到一所学校，并且
数据库会用他们独特的TeacherID跟踪这些人打印出多个Lynda Jones的时间表可能会让学生感到困惑。
所以我想阻止任何学校有一个以上的Lynda Jones，
这需要任何学校的唯一教师姓名。
I have a table: Schools,
and another table: Teachers
as one teacher may be assigned to more than one school I link these with a
third table: SchoolTeachers
I index (unique) this linking table on SchoolID and TeacherID to prevent
assigning a teacher to a school more than once.
So far, so good.
But School Administrators have difficulty with the idea that there might be more than one "Lynda Jones", teacher, assigned to one school, and that the
database will keep track of these individuals with their unique TeacherID,
and timetables printed out for more than one Lynda Jones may confuse
students.
So I want to prevent any school from having more than one Lynda Jones, that is require unique teacher names for any school.
" Lyle Fairfi ELD" <弥************ @ Invalid.Com>在消息中写道
新闻：Xn ******************* @ 130.133.1.4 ...
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
我有一张桌子：学校，
和另一张桌子：教师
作为一名教师可能被分配到一所以上的学校我将这些与第三张桌子联系起来：学校老师
我在SchoolID和TeacherID上索引（唯一）这个链接表，以防止将教师分配到学校不止一次。
到目前为止，这么好。
但是学校管理员很难想到可能会有超过一个Lynda Jones，老师，被分配到一所学校，并且
数据库会用他们独特的TeacherID跟踪这些人打印出多个Lynda Jones的时间表可能会让学生感到困惑。
所以我想阻止任何学校有一个以上的Lynda Jones，
这需要任何学校的独特教师姓名。
如何做到这一点？
我目前的解决方案 - >索引视图：
Sub CreateSQLObject（）
使用CurrentProject.Connection
。执行创建视图dbo.SchoolsandTeachers with SCHEMABINDING AS
SELECT st.SchoolTeacherID，s.SchoolID，s.SchoolName，t.TeacherID，
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeachers st ON s.SchoolID
= st.SchoolID JOIN dbo .Teachers t ON st.TeacherID = t.TeacherID"
。执行SET ARITHABORT ON
。执行创建独特的群集索引ixSchoolsTeachers ON
dbo.SchoolsandTeachers（SchoolTeacherID）"
。执行创建独特的指数ixSchoolsandTeachers ON
dbo.SchoolsandTeachers（SchoolID，TeacherName）"
结束<结束时
当然，这还没有在战斗中尝试过。但它似乎很有希望。
这些不会是巨大的表，所以我希望索引开销最小化。欢迎提出意见。
I have a table: Schools,
and another table: Teachers
as one teacher may be assigned to more than one school I link these with a
third table: SchoolTeachers
I index (unique) this linking table on SchoolID and TeacherID to prevent
assigning a teacher to a school more than once.
So far, so good.
But School Administrators have difficulty with the idea that there might be more than one "Lynda Jones", teacher, assigned to one school, and that the
database will keep track of these individuals with their unique TeacherID,
and timetables printed out for more than one Lynda Jones may confuse
students.
So I want to prevent any school from having more than one Lynda Jones, that is require unique teacher names for any school.
How to do this?
My current solution -> an indexed view:
Sub CreateSQLObject()
With CurrentProject.Connection
.Execute "CREATE VIEW dbo.SchoolsandTeachers WITH SCHEMABINDING AS
SELECT st.SchoolTeacherID, s.SchoolID, s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeachers st ON s.SchoolID = st.SchoolID JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID"
.Execute "SET ARITHABORT ON"
.Execute "CREATE UNIQUE CLUSTERED INDEX ixSchoolsTeachers ON
dbo.SchoolsandTeachers (SchoolTeacherID)"
.Execute "CREATE UNIQUE INDEX ixSchoolsandTeachers ON
dbo.SchoolsandTeachers (SchoolID, TeacherName)"
End With
End Sub
Of course, this has not yet been tried in battle. But it seems promising.
These will not be huge tables so I am hoping the indexing overhead will be
minimal. Comments are welcome.
另一种方法是创建具有相同名称的允许教师和
创建一个视图，如果学校有教师ID两个或更多
老师同样：
创建视图dbo.SchoolsandTeachers with SCHEMABINDING as
SELECT st.SchoolTeacherID，s .SchoolID，
CASE
WHEN
（
SELECT COUNT（*）来自dbo.SchoolTeachers st2
INNER JOIN dbo.Teachers t2 ON st2.TeacherID = t2.TeacherID
WHERE st2.SchoolID = st.SchoolID
AND t2.TeacherName = t .TeacherName
）> 1那么t.TeacherName +''（''+ CAST（t.TeacherID AS VARCHAR（15））+
'''''
ELSE t.TeacherName
END AS TeacherName，
s.SchoolName，t.TeacherID，
t.TeacherName FROM dbo.Schools s
JOIN dbo.SchoolTeachers st ON s.SchoolID = st.SchoolID
JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID
我不认为额外的开销太大了，特别是如果
TeacherName被编入索引。而且，除非学校有一个长期政策，就是雇用两名同名的教师（不太可能），这将更加贴近现实世界。它会产生漂亮的输出：
TeacherName
--------------
Jack Layton
Paul Martin（123）
Paul Martin（456）
Steven Harper
An alternative might be to create allow teachers with the same name and
create a view that would include the teachers ID if a school has two or more
teachers with the same:
CREATE VIEW dbo.SchoolsandTeachers WITH SCHEMABINDING AS
SELECT st.SchoolTeacherID, s.SchoolID,
CASE
WHEN
(
SELECT COUNT(*) FROM dbo.SchoolTeachers st2
INNER JOIN dbo.Teachers t2 ON st2.TeacherID = t2.TeacherID
WHERE st2.SchoolID = st.SchoolID
AND t2.TeacherName = t.TeacherName
) > 1 THEN t.TeacherName + '' ('' + CAST(t.TeacherID AS VARCHAR(15)) +
'')''
ELSE t.TeacherName
END AS TeacherName,
s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s
JOIN dbo.SchoolTeachers st ON s.SchoolID = st.SchoolID
JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID
I don''t think the extra overhead would be too drastic, especially if
TeacherName is indexed. And, unless a school has a standing policy of not
hiring two teachers with the same name, (unlikely), this would more closely
model the real world. And it would give a nice looking output:
TeacherName
--------------
Jack Layton
Paul Martin (123)
Paul Martin (456)
Steven Harper
这篇关于MSSQL查看他们可能会帮助的一些时间问题。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
