Re: Still Struggling...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Well, I learned something new. Let's try this again so that's it's readable.

*************************************************************************************

tblEmployees

**********

Inactive Yes/No

EmpID (PK) Autonumber, long integer

ClassID (FK to tblClassifications)

EmpTitleID (FK to tblEmpTitles)

PhoneID (FK to tblEmpTitles)

LN- txt

FN-txt

MI-txt



(sfrm) tblSiteEmp (1:1)
SiteEmpID (PK)

EmpID (FK) number, long integer
DeptsSubjectsID (FK) number, long integer
Address-txt
City-txt
State-txt
Emergency Info(Hospital, Medications, Allergies etc.)
DateEntered Date/Time
DateModified Date/Time


tblPhones(sfrm) 1:M


***************
PhoneID (PK) Autonumber, long integer

HomePhone-txt
CellPhone-txt
DistrictCell-txt
DateEntered-Date/Time
DateModified-Date/Time

tblTitles 1:M

*********

TitlesID (PK) Autonumber, long integer
TitleName-txt

tblSubjects 1:M
***********

SubjectsID (PK) Autonumber, long integer
SubjectName-txt


tblDepts 1:M
*********
DeptsID (PK) Autonumber, long integer
DeptsName-txt

tblDeptsSubjects M:M
************
DeptsSubjectsID (PK) Autonumber
SubjectsID (FK to tblSubjects)
DeptsID (FK to tblDepts)


tblClassifications 1:M

***************

ClassID (PK) Autonumber, long integer

ClassName-txt



tblTitlesEmps M:M
*************


TitlesEmpsID (PK) Autonumber

EmpID (FK to tblEmps)

TitlesID (FK to tblTitles)



*All FK keys are number, long integer

Relationships:
tblSiteEmp 1:1 tblEmps

tblPhones 1:M tblEmps

tblTitles 1:M tblTitlesEmps

tblClassifications 1:M tblEmps

tblTitlesEmps M:M tblEmps

tblSubjects 1:M tblDeptsSubjects

tblDepts 1:M tblDeptsSubjects

tblDeptsSubjects M:M tblSiteEmp
--
Aria W.


"Beetle" wrote:

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information. Thank
you! My comments are below (I'm combining posts. I didn't know how else to do
this):
Bruce M:
You could have a Titles table and an Employees table, with a EmployeeTitle
junction table. That may make some sense if several employees have the same
title, but if a lot of titles are unique that approach could become awkward.
My thinking is that there would be a main form based on tblEmployees, with a
subform based on the junction table.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes, I
still trying to get over having empty fields but I haven't made it this far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible title
that I can. I don't want any suprises here.

Bruce M:

If you would like to do something such as generate reports that list
employees by
title, the junction table is probably the best way to go (or the most
flexible, at least).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
You may do well to start a new thread on this specific topic. I'm not sure
I have the experience to advise you confidently that one choice is better
than another in a particular situation.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I know
I need to learn how to figure out some of these issues myself. I have to try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
The thing is that this database is about keys, but once you have an Employee
table you will probably use it again and again (as a linked table in other
projects), so it is well to
design it as carefully as can be done.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the hard
work at the beginning so that it will seem effortless in the end. Well, here
it is. I am willing to put in the effort but some days I am frustrated beyond
belief.

I had to add a phone table because there are a possiblity of 3 phone #s
that
a staff member may have aside from the room phone#, which I moved to
tblLocations per our previous discussion. I think this is a 1:M
relationship.

Bruce M:
One employee: several phones is one-to-many, but if a phone could have
several people answering it, depending on the time of day, you may need a
jPhonePerson unction table. StartTime and EndTime may be fields in the
junction table for the room phones. Ideally this would be linked to
scheduling, so that a class schedule would indicate when an employee could
be reached at a particular room phone (or something like that), but that
could become quite complex.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

Aria:
We also discussed department affiliation but I can't recall an instance
where staff will belong to more than one department.

Bruce M:
If so, you could just store the department in tblEmployees. However, be
sure of this, as it is moredifficult to rearrange the database later.

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of
information there. I had to really pay attention though and relate it to my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.
To take an extreme example, in a personnel database you would not have an Employees > table and a separate table for every Job Title in the organization; you'd be more likely to > have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, >DateFrom and DateTo columns to model the many-to-many relationship between >Employees and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is such a
wealth of information, instructions and suggestions that you put into your
replies that I can't post back right away; I'm studying, highlighting and
taking notes! :)

2. I've learned SO much from you (the collective you). I consider myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by some
of my own table names (yeah, I know that doesn't even begin to make sense to
you. I'll work on more logical naming). :)
I'm also confusing myself by thinking to hard and long about some of these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in, incrementally, to
get to the crux of a problem. I want to do that. This is starting to come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal with
later after everything is set. It's frustrating but I'm trying to keep it in
perspective.

5. See #3. I'm going to have to take a step back. I have been working on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me forward
by leaps and bounds. I understand that there are others who need help too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted). You have no idea how dejected I was when I first posted,
but every question answered unraveled the knot just a little bit more. I'm
going to take a couple of days and see if I can work out this employee table.
When I return, I would like to get back to tblLocations, tblKeys, etc. I hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

The Employee section of the db seems to be expanding. I'm still struggling
to work it out. As far as I can see, there seems to be some 1:1
relationships. As it stands now, there are at least 3 junction tables. One
thing is clear though, no matter how I work this, there will be empty fields
in some of the tables. I guess I *am* going to have to live with it.

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

***************************************************

When it comes to people connected to the church in some way or other things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So there
is an entity type People with these common attribute types, and a table can
represent this attribute type. People with different roles may well have
attribute types which are specific to their role, e.g. a pastor is likely to
have attribute types which a member of his congregation would not have. If I
can use an example from the C of E an attribute type for a C of E priest
might be Date of Ordination. So the entity type Pastors is a sub-type of the
entity type People. The way a sub-type is modelled in a relational data base
is by means of a one-to-one relationship. In the case of People and its
sub-type Pastors this would mean that there would be a table people with a
numeric primary key PersonID (don't use names as a primary key, they can be
duplicated), and a Pastors table also with a numeric primary key PersonID
(you can call it PastorID if you wish, but I prefer to keep the column names
the same). In the case of the Pastors table PersonID would also be a foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.
.

Relevant Pages

  • Re: Still Struggling...
    ... An employee would only be in the SoftballTeam table once, and likewise an employee on the softball team will have just one corresponding record in the main Employee table. ... You could have a Titles table and an Employees table, ... PersonID column in the People table, but the primary key of Pastors or other ... sub-type of people must be a straightforward long integer number data type, ...
    (microsoft.public.access.gettingstarted)
  • Re: Still Struggling...
    ... we were talking about *16* empty fields per Sub record. ... Beetle, I also think relationship type may have "stuck" because you used my ... This is just the part for Employees. ... A sub-type can of course have sub-types of its own; Chris Date in one of his ...
    (microsoft.public.access.gettingstarted)
  • Re: Still Struggling...
    ... PhoneID Autonumber, long integer ... EmpID (FK to tblEmps) ... fields in the Employees table", but I was just being lazy. ... A sub-type can of course have sub-types of its own; Chris Date in one of his ...
    (microsoft.public.access.gettingstarted)