MSSQL Views a tine problem they may help with.

Lyle Fairfield <MissingAddress@Invalid.Com> wrote in
news:Xns950E850C4D13CFFDBA@130.133.1.4:

Anyone know how to spellcheck Subjects?

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

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" <MissingAddress@Invalid.Com> wrote in message
news:Xns950E850C4D13CFFDBA@130.133.1.4...[color=blue]
> 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[/color]
be[color=blue]
> 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,[/color]
that[color=blue]
> is require unique teacher names for any school.
>[/color]

"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
news:Xns950E850C4D13CFFDBA@130.133.1.4...[color=blue]
> 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[/color]
be[color=blue]
> 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,[/color]
that[color=blue]
> 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[/color]
=[color=blue]
> 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.
>[/color]


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

"John Winterbottom" <assaynet@hotmail.com> wrote in
news:2jm065F12n4f5U1@uni-berlin.de:
[color=blue]
> "Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
> news:Xns950E850C4D13CFFDBA@130.133.1.4...[color=green]
>> 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[/color]
> be[color=green]
>> 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,[/color]
> that[color=green]
>> 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[/color]
>=[color=green]
>> 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.
>>[/color]
>
>
> 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)[/color]

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in
news:myjBc.403920$Ar.136435@twister01.bloor.is.net .cable.rogers.com:
[color=blue]
> We had two individuals with the same name in a 12 person department (and
> their wives had the same names as well!)[/color]

You have all the fun!!!

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

"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
news:Xns950E8F02837A3FFDBA@130.133.1.4...[color=blue]
> Good idea John! Thanks.
>
> hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
> Sounds good to me ....
>[/color]


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

"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
news:Xns950E8F02837A3FFDBA@130.133.1.4...[color=blue]
> "John Winterbottom" <assaynet@hotmail.com> wrote in
> news:2jm065F12n4f5U1@uni-berlin.de:
>[color=green]
> > "Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
> > news:Xns950E850C4D13CFFDBA@130.133.1.4...[color=darkred]
> >> 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[/color]
> > be[color=darkred]
> >> 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,[/color]
> > that[color=darkred]
> >> 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[/color]
> >=[color=darkred]
> >> 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.
> >>[/color]
> >
> >
> > 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)[/color]
>
> Good idea John! Thanks.
>
> hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
> Sounds good to me ....[/color]

What biting sarcasm. Like a tine to the eyeball.

Lyle Fairfield <MissingAddress@Invalid.Com> wrote:
[color=blue]
>hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
>Sounds good to me ....[/color]

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

"Tony Toews" <ttoews@telusplanet.net> wrote in message
news:1bdcd053cps185jqj5k7k223k9heh7t3mk@4ax.com... [color=blue]
> Lyle Fairfield <MissingAddress@Invalid.Com> wrote:
>[color=green]
> >hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
> >Sounds good to me ....[/color]
>
> Only if Martin is a -1 and Layton a -10.
>[/color]

This is the one that sume it up for me

http://www.cbc.ca/canadavotes/theca...cartoon/26.html


Ah politics ...

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" <MissingAddress@Invalid.Com> wrote in message
news:Xns950E850C4D13CFFDBA@130.133.1.4...[color=blue]
> 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[/color]
be[color=blue]
> 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,[/color]
that[color=blue]
> 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[/color]
=[color=blue]
> 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/)[/color]

"John Winterbottom" <assaynet@hotmail.com> wrote:
[color=blue][color=green][color=darkred]
>> >hmmmm ... just think ... calling Steven Harper, "Mr. Zero", all the time.
>> >Sounds good to me ....[/color]
>>
>> Only if Martin is a -1 and Layton a -10.
>>[/color]
>
>This is the one that sume it up for me
>
>http://www.cbc.ca/canadavotes/theca...cartoon/26.html[/color]

<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

"Alan Webb" <knogeek@hotmail.com> wrote in
news:6wsBc.146$JE6.40316@news.uswest.net:
[color=blue]
> 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.[/color]

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

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" <MissingAddress@Invalid.Com> wrote in message
news:Xns950F455AF7DE7FFDBA@130.133.1.4...[color=blue]
> "Alan Webb" <knogeek@hotmail.com> wrote in
> news:6wsBc.146$JE6.40316@news.uswest.net:
>[color=green]
> > 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.[/color]
>
> 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[/color]
shows[color=blue]
> 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,[/color]
with[color=blue]
> 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[/color]
names,[color=blue]
> 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[/color]
humans,[color=blue]
> 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?[/color]
Change[color=blue]
> 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[/color]
school[color=blue]
> timetables. Shall we have two Class 87's (typically the seventh grade[/color]
eight[color=blue]
> 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/)[/color]