MSSQL Views a tine problem they may help with.

[Lyle Fairfield]

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.SchoolsandT eachers WITH SCHEMABINDING AS
SELECT st.SchoolTeache rID, s.SchoolID, s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeach ers st ON s.SchoolID =
st.SchoolID JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID"

.Execute "SET ARITHABORT ON"

.Execute "CREATE UNIQUE CLUSTERED INDEX ixSchoolsTeache rs ON
dbo.SchoolsandT eachers (SchoolTeacherI D)"

.Execute "CREATE UNIQUE INDEX ixSchoolsandTea chers ON
dbo.SchoolsandT eachers (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]

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

[Douglas J. Steele]

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...

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.

[John Winterbottom]

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

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.SchoolsandT eachers WITH SCHEMABINDING AS
SELECT st.SchoolTeache rID, s.SchoolID, s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeach ers st ON s.SchoolID = st.SchoolID JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID"

.Execute "SET ARITHABORT ON"

.Execute "CREATE UNIQUE CLUSTERED INDEX ixSchoolsTeache rs ON
dbo.SchoolsandT eachers (SchoolTeacherI D)"

.Execute "CREATE UNIQUE INDEX ixSchoolsandTea chers ON
dbo.SchoolsandT eachers (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.

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.SchoolsandT eachers WITH SCHEMABINDING AS
SELECT st.SchoolTeache rID, s.SchoolID,
CASE
WHEN
(
SELECT COUNT(*) FROM dbo.SchoolTeach ers 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.TeacherI D AS VARCHAR(15)) +
')'
ELSE t.TeacherName
END AS TeacherName,
s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s
JOIN dbo.SchoolTeach ers 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

[Lyle Fairfield]

"John Winterbottom" <as******@hotma il.com> wrote in
news:2j******** *****@uni-berlin.de:

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

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.SchoolsandT eachers WITH SCHEMABINDING
AS
SELECT st.SchoolTeache rID, s.SchoolID, s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeach ers st ON
s.SchoolID

=

st.SchoolID JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID"

.Execute "SET ARITHABORT ON"

.Execute "CREATE UNIQUE CLUSTERED INDEX ixSchoolsTeache rs ON
dbo.SchoolsandT eachers (SchoolTeacherI D)"

.Execute "CREATE UNIQUE INDEX ixSchoolsandTea chers ON
dbo.SchoolsandT eachers (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.

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.SchoolsandT eachers WITH SCHEMABINDING AS
SELECT st.SchoolTeache rID, s.SchoolID,
CASE
WHEN
(
SELECT COUNT(*) FROM dbo.SchoolTeach ers 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.TeacherI D AS
VARCHAR(15)) +
')'
ELSE t.TeacherName
END AS TeacherName,
s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s
JOIN dbo.SchoolTeach ers 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 (0)

Good idea John! Thanks.

hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
Sounds good to me ....

--
Lyle
(for e-mail refer to http://ffdba.com/)

[Lyle Fairfield]

"Douglas J. Steele" <NOSPAM_djsteel e@NOSPAM_canada .com> wrote in
news:my******** ************@tw ister01.bloor.i s.net.cable.rog ers.com:

We had two individuals with the same name in a 12 person department (and
their wives had the same names as well!)

You have all the fun!!!

--
Lyle
(for e-mail refer to http://ffdba.com/)

[John Winterbottom]

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

Good idea John! Thanks.

hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
Sounds good to me ....

:) yes - more than one Steven Harper; that's a "real" nightmare

[rkc]

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

"John Winterbottom" <as******@hotma il.com> wrote in
news:2j******** *****@uni-berlin.de:

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

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.SchoolsandT eachers WITH SCHEMABINDING
AS
SELECT st.SchoolTeache rID, s.SchoolID, s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s JOIN dbo.SchoolTeach ers st ON
s.SchoolID

=

st.SchoolID JOIN dbo.Teachers t ON st.TeacherID = t.TeacherID"

.Execute "SET ARITHABORT ON"

.Execute "CREATE UNIQUE CLUSTERED INDEX ixSchoolsTeache rs ON
dbo.SchoolsandT eachers (SchoolTeacherI D)"

.Execute "CREATE UNIQUE INDEX ixSchoolsandTea chers ON
dbo.SchoolsandT eachers (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.

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.SchoolsandT eachers WITH SCHEMABINDING AS
SELECT st.SchoolTeache rID, s.SchoolID,
CASE
WHEN
(
SELECT COUNT(*) FROM dbo.SchoolTeach ers 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.TeacherI D AS
VARCHAR(15)) +
')'
ELSE t.TeacherName
END AS TeacherName,
s.SchoolName, t.TeacherID,
t.TeacherName FROM dbo.Schools s
JOIN dbo.SchoolTeach ers 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 (0)

Good idea John! Thanks.

hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
Sounds good to me ....

What biting sarcasm. Like a tine to the eyeball.

[Tony Toews]

Lyle Fairfield <Mi************ @Invalid.Com> wrote:

hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
Sounds good to me ....

Only if Martin is a -1 and Layton a -10.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[John Winterbottom]

"Tony Toews" <tt****@teluspl anet.net> wrote in message
news:1b******** *************** *********@4ax.c om...

Lyle Fairfield <Mi************ @Invalid.Com> wrote:

hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
Sounds good to me ....

Only if Martin is a -1 and Layton a -10.

This is the one that sume it up for me

http://www.cbc.ca/canadavotes/thecam...artoon/26.html
Ah politics ...