Re: Still Struggling...
- From: Beetle <Beetle@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 2 Jul 2008 10:49:04 -0700
I posted this reply through Access Monster but am attempting to post
it here just to see if it works.
BTW - "Mark", who's real name is Steve, is just a troll that shows up every
now and
then to slum for work, which is against the rules of the group, but he
doesn't care.
Just ignore him.
On to the reply;
EmpID would be the PK for both tables. In tblSiteEmp it acts as both the >>PK for that table and the FK to tblEmployees.
Really?! Both tables?
Yes really. Since each table is only allowed one record per employee, then
EmpID will be a unique value for each record in both tables. Therefore it
qualifies as the PK for both tables.
So in this relationship tblEmployees is again the parent, and tblPhones
is the child. In this case we are allowed to have more than one child
per parent, so the child table does need to have it's own PK.
You're killing me here! :-) What?! ...understanding flowing out. Is it
because in the first example it was 1:1 and in this one it's 1:M?
Yes. Different relationship, different rules. In this case EmpID (the PK
of the parent table) can be repeated many times in the child table, so
it does not qualify as the PK of the child ("many") table. The child table
must have it's own unique PK, like PhoneID.
In fact, EmpTitleID does not need to exist in *either* table.
Whoa, *what*?! <trying hard not to cry> This is hard. I needed this[quoted text clipped - 3 lines]
explanation because some things I was doing without really understanding why;
have a combo PK. That was one of the original tables I had and yet I don't do
the same thing here.
there is nothing to prevent the same title being assigned to the same
employee many times over.
There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really understand
how you would look at that and *know* it means the same title assigned over
and over to the same employee. I'm going to have to get a handle on this;
more homework.
Perhaps it would help your understanding if we take a look at how the data
would actually appear in the table. First, let's look at the table structure
as you had it;
tblTitlesEmps M:M
*************
TitlesEmpsID (PK) Autonumber
EmpID (FK to tblEmps)
TitlesID (FK to tblTitles)
With the above structure, the only value that has uniqueness enforced is
TitlesEmpsID (the PK), unless you put a unique index on EmpID and
TitlesID (which I'm reasonably sure you didn't do in this case). Now, let's
suppose you have an employee named John Smith whose EmpID is 1.
Let's also suppose that your Titles table has a description like
"Economics Professor" and that the TitleID for this description is 5.
There is nothing preventing the data in your junction table from looking
like;
TitlesEmpsID EmpID TitlesID
1 1 5
2 1 5
3 1 5
As you can see, the same employee can have the same title assigned
multiple times, because the only "rule" is that TitlesEmpsID must be
unique for each record.
Now, if we get rid of TitlesEmpsID, and use EmpID and TitlesID as
a combined two field PK then;
EmpID TitlesID
1 5
1 5 <<<<<this would not be allowed
1 3
1 4
With this structure, the same employee can have more than one title
assigned, but never the same one twice, because the combination of
the two values must be unique.
Updated comment: I'm almost afraid to ask because I think I should have this
down since you've already explained but, "describe a persons role within a
Department"? Isn't that the same as EmpTitle?
You might be right. I would have to know a little bit more to say for sure.
From what I do know, it seems like you need;
1) A way to describe what Dept.(s) an employee works in. In this case
tblDepts holds descriptive information about the different departments
an tblEmpDepts (the junction table) controls the relationship.
2) A way to describe what an employee does within each Dept. So the
descriptive information like "Dept. Chair", "Physics Prof.", or
"Custodian"
would be held in? tblTitles? tblSubjects?. Your choice I guess. Then the
relationship would be managed by tblEmpTitles (or whatever you call it).
So maybe it will turn out that tblTitles an tblSubjects are essentially the
same thing. Maybe you can actually get rid of a couple of tables!
WoooooHooooo!<g>
As far as the Subs credentials, that would be handled in the same manner
as the full time staff. If they have some type of credential, then there will
be a record in tblEmpClassifications to reflect that fact. If not, there won't
be a record. Simple.
???...not following you. I don't track site staff credentials. The district[quoted text clipped - 7 lines]
handles that. As long as subs have a teaching credential, they can sub in
long-term sub. I just want to be ready. But I don't understand what you are
proposing; just add a field to tblEmployees?
No, I am not proposing that you add any fields at all. Perhaps I can explain
it a little better. Your tblEmployees holds the *names* of all potential
employees, whether they are full time or subs. tblClassifications holds
the descriptions of all possible credential types. tblEmpClassifications
manages the relationship between the two. Therefore, *if* a sub
had some type of credential, and *if* you wanted to store that data in
your application, then a record would be created in tblEmpClassifications
just the same as it would for a full time employee. If you don't want
to track that info, then no record is ever created. No empty fields or
anything to worry about.
--
_________
Sean Bailey
.
- References:
- Re: Still Struggling...
- From: Aria
- Re: Still Struggling...
- Prev by Date: Re: Still Struggling...
- Next by Date: RE: Setting up of a training database
- Previous by thread: Re: Still Struggling...
- Next by thread: I receive an error message Type mismatch
- Index(es):
Relevant Pages
|
