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

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

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

[Lyle Fairfield]

"John Winterbottom" <as******@hotmail.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.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.

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 (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_djsteele@NOSPAM_canada.com> wrote in
news:my********************@twister01.bloor.is.net .cable.rogers.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******@hotmail.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.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.

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 (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****@telusplanet.net> wrote in message
news:1b********************************@4ax.com...

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

[Alan Webb]

Lyle,
As you are discovering, people don't have attributes that easily lend
themselves to an elegant significant primary key. We are all so alike.
Your school has an Employee ID that they issue for payroll purposes which
will uniquely identify a teacher. I'd use that, if it can be made available
to your database. Otherwise, creating an arbitrary key like an autonumbered
RowID and printing it in your reports with the teacher's name should be
enough.

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

[Tony Toews]

"John Winterbottom" <as******@hotmail.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

<chuckle> Good one.

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

[Lyle Fairfield]

"Alan Webb" <kn*****@hotmail.com> wrote in
news:6w*****************@news.uswest.net:

Lyle,
As you are discovering, people don't have attributes that easily lend
themselves to an elegant significant primary key. We are all so alike.
Your school has an Employee ID that they issue for payroll purposes
which will uniquely identify a teacher. I'd use that, if it can be made
available to your database. Otherwise, creating an arbitrary key like
an autonumbered RowID and printing it in your reports with the teacher's
name should be enough.

I made my original post to show a use for views. Views do not seem to be
discussed here, nor in MS's ADP group to any great extent. My example shows
how a view can be indexed, and how the nature of a so called many-to-many
relationship between two tables can be constrained in this way.

I was not asking for advice about how to identify teachers uniquely. In
twenty-years of working with desktop databases I have learned how to do
that. I have also learned that a solution that the database engine
understands and can use, is not necessarily a solution that a system of
humans can understand and use.

Of course, publishing teachers’ payroll numbers is out of the question.

I cannot imagine that distributing timetables to a school's community, with
one sheet being for teacher
Lynda Jones (406)
and another for
Lynda Jones (214 763)
is an administratively sound idea.

Nor would I wish to contribute to the bewilderment of 13 year olds by
giving them a time table which indicated they attended
Lynda Jones (406) for science at 09:40
and
Lynda Jones (214 763) for social science at 13:00.

Not would I wish to explain to parents that they had actually interviewed
the wrong Lynda Jones about the wrong Esmeralda, (these incidents actually
happen), and that it was unnecessary to lengthen Esmeralda’s skirts.

Of course, we ARE going to attach a unique identifier to each of the names,
Lynda Jones. But the solution of the unique index – unique name requires a
human created unique identifier, one which, if one has any faith in humans,
will be appropriate for that school. So we may have
"Lynda Jones 406" and "Lynda Jones 214 763"
or
"Lynda A Jones" and "Lynda B Jones"
or
"Lanky Jones" and "Extra Kilo Jones".

We also create the potential for change.
Suppose we chose "Lynda Jones 69" for one of our teachers and she objected
to this designation. We would go the Teacher Table and change her name to
"Lynda Jones 666 " or whatever. If we were using arbitrary numbers to
create unique designations and Lynda Jones was number 69, then what? Change
the program? Delete and recreate Lynda Jones?

Now, let's apply our solutions to Schools and Classes rather than Schools
and Teachers, something we are likely to do in addressing elementary school
timetables. Shall we have two Class 87's (typically the seventh grade eight
class) differentiated by number, say, Class 87 - 87 and Class 87 - 88?
Would this confuse anyone?

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

[Alan Webb]

Lyle,
I misunderstood. Most of my designs include an arbitrary row id useful to
me when I need to uniquely identify a row. They also include any additional
columns needed to ensure I capture my customer's identifiers for an instance
of an entity. These days most of the databases I work with have been in
production for some time and it is not possible to revisit the means by
which an instance of an entity is uniquely identified. So if I need
something of my own I tack it on to the existing attributes of the entity.
The thing I misunderstood is that you were posting a theoretical question.
I'll go back and re-read the thread.

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

"Alan Webb" <kn*****@hotmail.com> wrote in
news:6w*****************@news.uswest.net:

Lyle,
As you are discovering, people don't have attributes that easily lend
themselves to an elegant significant primary key. We are all so alike.
Your school has an Employee ID that they issue for payroll purposes
which will uniquely identify a teacher. I'd use that, if it can be made
available to your database. Otherwise, creating an arbitrary key like
an autonumbered RowID and printing it in your reports with the teacher's
name should be enough.
I made my original post to show a use for views. Views do not seem to be
discussed here, nor in MS's ADP group to any great extent. My example

shows how a view can be indexed, and how the nature of a so called many-to-many
relationship between two tables can be constrained in this way.

I was not asking for advice about how to identify teachers uniquely. In
twenty-years of working with desktop databases I have learned how to do
that. I have also learned that a solution that the database engine
understands and can use, is not necessarily a solution that a system of
humans can understand and use.

Of course, publishing teachers' payroll numbers is out of the question.

I cannot imagine that distributing timetables to a school's community, with one sheet being for teacher
Lynda Jones (406)
and another for
Lynda Jones (214 763)
is an administratively sound idea.

Nor would I wish to contribute to the bewilderment of 13 year olds by
giving them a time table which indicated they attended
Lynda Jones (406) for science at 09:40
and
Lynda Jones (214 763) for social science at 13:00.

Not would I wish to explain to parents that they had actually interviewed
the wrong Lynda Jones about the wrong Esmeralda, (these incidents actually
happen), and that it was unnecessary to lengthen Esmeralda's skirts.

Of course, we ARE going to attach a unique identifier to each of the names, Lynda Jones. But the solution of the unique index - unique name requires a
human created unique identifier, one which, if one has any faith in humans, will be appropriate for that school. So we may have
"Lynda Jones 406" and "Lynda Jones 214 763"
or
"Lynda A Jones" and "Lynda B Jones"
or
"Lanky Jones" and "Extra Kilo Jones".

We also create the potential for change.
Suppose we chose "Lynda Jones 69" for one of our teachers and she objected
to this designation. We would go the Teacher Table and change her name to
"Lynda Jones 666 " or whatever. If we were using arbitrary numbers to
create unique designations and Lynda Jones was number 69, then what? Change the program? Delete and recreate Lynda Jones?

Now, let's apply our solutions to Schools and Classes rather than Schools
and Teachers, something we are likely to do in addressing elementary school timetables. Shall we have two Class 87's (typically the seventh grade eight class) differentiated by number, say, Class 87 - 87 and Class 87 - 88?
Would this confuse anyone?

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