[thelist] create synonym

David McCreath mccreath at ak.net
Thu Dec 7 00:27:22 CST 2000


Hi, Rudy --

> > In Oracle, a public or private synonym is often used to eliminate
> > the need to specify a username when requesting a table in another
> > schema. SQL Server does not provide public or private synonyms.
>
> tommy chong voice:  bummer, man
>
> (aside: just caught a few moments the other night of some sitcom with an
> old hippie in a fotomat i could swear is tommy chong, one of my personal
> alltime favourite heroes)

As  über-pop-craftsman Nick Lowe once said, "There's nothing wrong with
blatantly wearing your influences on your sleeve."

> anyhow, if the purpose of the synonym is to make some other user's table
> more easily addressible, perhaps a view might do the same job?
>
>    create view myview
>     as select * from otheruser.histable

Yeah, I think it would serve the same purpose -- but that makes me wonder:
Do Oracle and Informix also use views? If so, what's the diff?

> of course, you should be aware that "select star is bad"

acknowledged and practiced. :)

> what did your informix dba say you should use the synonym for, anyhow?

Knowing what I know now, I don't think it would have actually been the right
solution. I'm working on an application that needs display and use the
district standards for language and math. A standard generally looks like
this:

Standard (Grade) Level : Area : Topic : Standard

A specific example would be (this is a fake one):

1st Grade : Math : Concepts : Understands arithmetic

Every grade has the same set of topics and then a varying number of specific
standards under that topic. So my initial database structure was very
granular:

table: StandardLevel (the standards aren't just based on grade)
       { StandardLevelID (pk)
         StandardLevelName }

table: MathTopic
       { MathTopicID (pk)
         MathTopicName }

table: MathStandard
       { MathStandardID  (pk)
         StandardLevelID (fk1)
         MathTopicID     (fk2)
         MathStandardDesc }

The MathTopics and MathStandards tables were repeated for the Language/Oral,
Language/Writing, and Language/Reading standards. My reasoning was that we
could add  new standards as they are developed and approved (say for
spelling) by adding two new tables. It would possibly make for some tricky
programming on the web pages, but I was trying to eliminate any redundant
data.

Well, the DBA and my boss said that they thought that would be TOO granular
and create programming headaches and to lump all the Topics together into
one table and all the Standards together into one table, adding a column to
each to flag the Standard or Topic by subject (Math, Language, whatever). So
you lose some of the tight control, but possibly simplify the programming.
(I think they were thinking in mainframe terms; this web stuff is freaky to
them.)

The DBA suggested the synonym thing as a way to address the Topics table
with a different name, but without duplicating the actual table. But that
doesn't seem like an appropriate use to me.

I've since decided that I'm going to add one more table called Subjects:

table: Subject
       { SubjectID (pk)
         SubjectName }

The Topic and Standard tables will each carry a column that refers to that.
I think that's a pretty good compromise.

Any insight is, as always, welcome. Er...if you understand what the heck I'm
talking about... :)

Dave





More information about the thelist mailing list