Database set up help

Database set up help

I am starting to wonder if my database is set up incorrectly. It
started wit
h two tables: Employee and Termination. These tables have a
relationship wit
h my ESCID (Employee Status Change ID) which is in my Employee table
and the
y work great.

However, my bosses wanted me to add Security Options to the picture. So
I ad
ded some more tables and now I feel that what I have done is actually
more w
ork than what I could have done and none of my queries are coming out
right.
So here I am.......asking for help.

The security options goes like this:
1. User needs to enter contact information (these fields come from the
Emplo
yee table)
2. After entering contact information the user makes a selection from
the Ma
in Menu of 38 choices. (I added a MMO table to the database [Main Menu
Optio
ns] and entered the 38 choices as individual fields with a yes/no data
type)
The user gets to select one or more options off this main menu
a. Depending on which options are chosen from the Main Menu allows
users to
choose other options.
3. If a user picks any of the 1st three options off the Main Menu the
next t
hing they get to choose from is a choice of Applications. (There are 20
appl
ications they can choose from). So for each application I set up an
individu
al table. I did this because not only can they choose an
"Application" but w
hen they do choose the application they have to make the security
selections
off that application and the security selections look something like
this (
for say the user picked the ACP application code out of the 20 choices
given
):
Application Code | ECM | U.S. | Canada

ACP100 | y/n | y/n | y/n
ACP200 | y/n | y/n | y/n
ACP300 | y/n | y/n | y/n
ACP400 | y/n | y/n | y/n

("|" = table columns)

Like I stated earlier there are 20 application codes and each one has
anywhe
re between 12 and 50+ codes within that application. Users also get a
choice
of either ECM, U.S., or Canada (represented as a checkbox).
So, not only did I give each application its own table but I also have
a ton
(and I mean a ton) of fields within the tables. For example, in the
ACP tab
le above you notice only 4 ACP codes (100,200,300,400) however in my
table t
hat is 12 fields: ACP100ECM, ACP100US, ACP100CANADA, ACP200ECM,
ACP200US, AC
P200CANADA and so on........

4. If a user picks # 4 off the Main Menu (LogPro Department) instead of
one
of the 1st three choices they get to choose one or more of the LogPro
Depart
ments. So I set up another table "LogPro" and put every department
(there ar
e 19) as a yes/no data type for checkboxes.

So, now as you can hopefully see I have a database with over 20 tables.
Am I
doing this correct or did I royally screw it up??

Any help, advice, or guidance would be greatly appreciated.

-Justine

Justine,

To first of all answer your direct question, it's royal.

I suspect a detailed revision of the database design is beyond the scope
of a newsgroup discussion at this point. If you want to get it right, I
suggest you take the time to read up on normalisation. There are some
references to some good information he
http://home.bendbroadband.com/conrad...abaseDesign101

The key principle to understand, I think, is the distinction between
data entities/categories, and the data itself. The data entities define
your fields and tables. You have allowed the data itself to define your
fields and tables. As an example, the choice of ECM/US/Canada is data.
Maybe the data category is Region or something. So to have a separate
field for each choice is called the "fields as data trap". There should
just be one field, to represent the data entity, and the "ECM" or"US" or
"Canada" is entered as the data in that field. Similarly, I do not
understand your project well enough to advise specifically, but I am
absolutely sure that the idea of a separate table for each Application
will drive you to despair. You need to set up the tables such that each
Application is entered into a record in an Applications table, and then
any data entity that can have more than one value for each application
(i.e. there is a one-to-many relationship) needs to be in a related
table, and each related item becomes a separate record (not field!) in
that related table.

--
Steve Schapel, Microsoft Access MVP


JNariss wrote:
Database set up help

I am starting to wonder if my database is set up incorrectly. It
started wit
h two tables: Employee and Termination. These tables have a
relationship wit
h my ESCID (Employee Status Change ID) which is in my Employee table
and the
y work great.

However, my bosses wanted me to add Security Options to the picture. So
I ad
ded some more tables and now I feel that what I have done is actually
more w
ork than what I could have done and none of my queries are coming out
right.
So here I am.......asking for help.

The security options goes like this:
1. User needs to enter contact information (these fields come from the
Emplo
yee table)
2. After entering contact information the user makes a selection from
the Ma
in Menu of 38 choices. (I added a MMO table to the database [Main Menu
Optio
ns] and entered the 38 choices as individual fields with a yes/no data
type)
The user gets to select one or more options off this main menu
a. Depending on which options are chosen from the Main Menu allows
users to
choose other options.
3. If a user picks any of the 1st three options off the Main Menu the
next t
hing they get to choose from is a choice of Applications. (There are 20
appl
ications they can choose from). So for each application I set up an
individu
al table. I did this because not only can they choose an
"Application" but w
hen they do choose the application they have to make the security
selections
off that application and the security selections look something like
this (
for say the user picked the ACP application code out of the 20 choices
given
):
Application Code | ECM | U.S. | Canada

ACP100 | y/n | y/n | y/n
ACP200 | y/n | y/n | y/n
ACP300 | y/n | y/n | y/n
ACP400 | y/n | y/n | y/n

("|" = table columns)

Like I stated earlier there are 20 application codes and each one has
anywhe
re between 12 and 50+ codes within that application. Users also get a
choice
of either ECM, U.S., or Canada (represented as a checkbox).
So, not only did I give each application its own table but I also have
a ton
(and I mean a ton) of fields within the tables. For example, in the
ACP tab
le above you notice only 4 ACP codes (100,200,300,400) however in my
table t
hat is 12 fields: ACP100ECM, ACP100US, ACP100CANADA, ACP200ECM,
ACP200US, AC
P200CANADA and so on........

4. If a user picks # 4 off the Main Menu (LogPro Department) instead of
one
of the 1st three choices they get to choose one or more of the LogPro
Depart
ments. So I set up another table "LogPro" and put every department
(there ar
e 19) as a yes/no data type for checkboxes.

So, now as you can hopefully see I have a database with over 20 tables.
Am I
doing this correct or did I royally screw it up??

Any help, advice, or guidance would be greatly appreciated.

-Justine

Thank you kindly for taking the time to read this and offer your
advice. I will read up on the link you supplied and see about a
revision for my database.

-Justine

Okay.......so I briefly read about normalization and I am was way
off!!! However, I think I am starting to enter the right state of mind.
Since data has not been entered into this database, in reality I can
start over with the Security Request tables.

Basicallly if I create a table called tblSecurityRequest and use these
fields:

SRID (Primary Key)
ESCID (Foreign Key)
MainMenuOptions (data type = lookup field to a tblMainMenuOptions with
only the Option choices as yes/no field data types)
LogProDepartments (data type = lookup field to tblLogPro with only the
Log Pro Options as yes/no field data types)
Applications (data type = lookup field to tblApplications with only the
Applications as yes/no data type)
Region (data type = lookup field to tblRegion with only the Regions
listed as yes/no data types [regions are ECM, US, Canada])

will I be on the right track? And by creating tblSecurityRequest with
the lookup fields as yes/no I will still be able to set up a form like
this example:

ACP Application Codes ECM US Canada
(y/n = a checkbox)

ACP100 y/n y/n y/n
ACP200 y/n y/n y/n
ACP300 y/n y/n y/n
ACP400 y/n y/n y/n
and so on..............

Or would the tblApplications have to be of text data type and leave the
Region as yes/no data type?

Once again, I appreciate any and all help.

-Justine



-Justine

Justine,

I haven't quite got a grasp on your data structure. You appear to be
fond of Yes/No data type fields. My impression is that these are
generally not applicable to the data you are dealing with. My
impression is that you willget along a lot better if you pretend that
Access does not provide the option of a Yes/No data type.

Just to focus on one small corner of your overall system, as a way of
illustrating the concept - Applications and Regions. Well I think I
understand "region" but I don't really know what an application is, in
the context of how you are using it. But you have these things called
applications, and you are listing them in a tblApplications table. Very
nice. And each Application is identified by a unique Application Code,
as you have shown in your example. Ok, is each Application associated
with only one Region? Or can each Application be associated with more
than one Region? If each Application is associated with only one
Region, you need a Region field in the tblApplication table. Just one
field. This can be Text data type, where the data entered will be "ECM"
or "US" or "Canada", that will work fine, and the data entry on your
form can be done via a combobox where you select from one of the 3
Region values. Or it could be a Number data type, where each of the 3
regions is allocated a numerical value. This will also work fine, and
would be applicable if you wanted to use an Option Group on your form
for data entry via a selection of one of 3 Options Buttons within an
Option Group. If, on the other hand, each Application can be associated
with more than one Region, you have a one-to-many relationship, and as
such need a separate table to record the Regions for each Application.
Data entry in this case would be done via a subform.

Hope that starts to clarify.

By the way, if each Application is only associated with one Region, and
if each SecurityRequest is only associated with one Application, then
you would not have a Region field in the tblSecurityRequest table... by
entering the Application, you automatically already know the Region by
virtue of the Region entry in the tblApplications table. Hope that
makes sense - once again, this is just an example of the thinking required.

--
Steve Schapel, Microsoft Access MVP


JNariss wrote:
Okay.......so I briefly read about normalization and I am was way
off!!! However, I think I am starting to enter the right state of mind.
Since data has not been entered into this database, in reality I can
start over with the Security Request tables.

Basicallly if I create a table called tblSecurityRequest and use these
fields:

SRID (Primary Key)
ESCID (Foreign Key)
MainMenuOptions (data type = lookup field to a tblMainMenuOptions with
only the Option choices as yes/no field data types)
LogProDepartments (data type = lookup field to tblLogPro with only the
Log Pro Options as yes/no field data types)
Applications (data type = lookup field to tblApplications with only the
Applications as yes/no data type)
Region (data type = lookup field to tblRegion with only the Regions
listed as yes/no data types [regions are ECM, US, Canada])

will I be on the right track? And by creating tblSecurityRequest with
the lookup fields as yes/no I will still be able to set up a form like
this example:

ACP Application Codes ECM US Canada
(y/n = a checkbox)

ACP100 y/n y/n y/n
ACP200 y/n y/n y/n
ACP300 y/n y/n y/n
ACP400 y/n y/n y/n
and so on..............

Or would the tblApplications have to be of text data type and leave the
Region as yes/no data type?

Once again, I appreciate any and all help.

-Justine



-Justine

Wow.........this is great.......I did not think anyone would reply or
try to help this database out. I really appreciate you taking the time
to read my post.

Okay, let's see...I choose the y/n data type because I am using
InfoPath to create my form. I like the way it has the ability to hide
sections and produce them when someone selects a choice. So this form
will work like this:

1. User opens up form and enters Employee Information in the fields
from tblEmployee.
2. The next thing they will see is the Main Menu from the fields in
tblMainMenuOptions. This is not a dropdown menu it is a checkbox menu
because users can check off as many options from the Main Menu that
they need.

For example: 2 of the 38 options in my main menu are BPCS Applications
and Logistics Pro. (These 2 options are how I came about my
tblApplications and tblLogPro.) If a user selects both these options
off the Main Menu then the form will unhide the Applications section
and the LogPro Department section giving the users the ability to
choose department(s) and Applications.

3. Once those menus open up and the user selects a department or
departments from the LogPro section that section is complete. However,
when the user starts making selections off the Application section the
individual application sections start to open giving the user the
ability once again to choose more options off each of the Application
sections.

For Example: I have 19 Applications on my Application section. Each
Application is a 3 letter code: ACP = Accounts Payable, ACR = Accounts
Receiveable, BIL = Billing and so on...... If a user puts a check next
to ACP and BIL then those hidden sections will appear on the form with
all the options they consist of.

4. Once the Applications menus open and the user sees them, the user
can start to check off application codes on those menus.

For Example: if ACP Application Codes opens the user will see my
example from above:

ACP Application Codes ECM US Canada
(y/n = a checkbox)

ACP100 y/n y/n y/n
ACP200 y/n y/n y/n
ACP300 y/n y/n y/n
ACP400 y/n y/n y/n
and so on..............

Each user is allowed to select ACP100 ECM, US, or Canada if they need.
Or they may just need to check ACP US and none of the others. So yes, a
user is allowed to select from more than one application code.

I set up a database with this so far:

tblSecurityRequestIDs
- this table holds all my primary keys for each table I created
below
tblEmployee
- this table holds all the employee information such as name,
title, pay number, start date, requested by and so one
tblRegions
- this table has 5 fields
- REGID *
- SRID (foreign key to tblSecurityRequestIDs)
-ECM
- US
- Canada
tblMainMenuOptions
- this table consists of 40 fields (one for each option on the Main
Menu, MMOID* and SRID)
tblApplications
- this table consists of 21 fields (one for each application, APPID*,
and SRID)
tblLogProDept
- this table consists of 21 fields (one for each department, LPID*,
and SRID)

* = Primary Keys

Then I have 19 other tables which are all named tbl and their app code:
tblACP, tblACR, tblBIL, tblBOM, tblCAP, tblCST, tblDRP, tblFOR, tblINV,
tblJIT, tblMDM, tblMFS, tblMPS, tblMRP, tblORD, tblPUR, tblSAL, tblSFC,
and tblZZZ

Each of these tables has a primary key of its application code name and
the word ID: ACPID, ACRIS, BILID, BOMID, and so on and a field for each
of its selections.

whew..................yeah I know lots of tables.

I have one to many relationships going from my main table:
tblSecurityRequestIDs to all my other tables. Is this right so far b/c
I feel I may be onto something here?

But........my next question is if I have a separate tblRegion how will
I be able to produce a form like the one above in which a user puts a
check in ACP100 - US and the table shows under ACP100 that it is region
US?

Just to make you happy ......of the tables I have set up only Main Menu
Options and Regions are of yes/no data types. Each other table I kept
as text data type. Is this correct?

Well I have babbled about my database enough. Please let me know if I
am onto the right track.

Thanks,
Justine

Justine,

Can we please just step back a moment? Leaving aside the tables you've
got now, and the forms etc,... are you able to imagine that I know
absolutely nothing about the industry you work in, and give me a simple
summary in ordinary English of the purpose of this application?
Presumably part of the baseline here is that you are recording
information about something... in a nutshell what is that "something"?

--
Steve Schapel, Microsoft Access MVP


JNariss wrote:
Wow.........this is great.......I did not think anyone would reply or
try to help this database out. I really appreciate you taking the time
to read my post.

Okay, let's see...I choose the y/n data type because I am using
InfoPath to create my form. I like the way it has the ability to hide
sections and produce them when someone selects a choice. So this form
will work like this:

1. User opens up form and enters Employee Information in the fields
from tblEmployee.
2. The next thing they will see is the Main Menu from the fields in
tblMainMenuOptions. This is not a dropdown menu it is a checkbox menu
because users can check off as many options from the Main Menu that
they need.

For example: 2 of the 38 options in my main menu are BPCS Applications
and Logistics Pro. (These 2 options are how I came about my
tblApplications and tblLogPro.) If a user selects both these options
off the Main Menu then the form will unhide the Applications section
and the LogPro Department section giving the users the ability to
choose department(s) and Applications.

3. Once those menus open up and the user selects a department or
departments from the LogPro section that section is complete. However,
when the user starts making selections off the Application section the
individual application sections start to open giving the user the
ability once again to choose more options off each of the Application
sections.

For Example: I have 19 Applications on my Application section. Each
Application is a 3 letter code: ACP = Accounts Payable, ACR = Accounts
Receiveable, BIL = Billing and so on...... If a user puts a check next
to ACP and BIL then those hidden sections will appear on the form with
all the options they consist of.

4. Once the Applications menus open and the user sees them, the user
can start to check off application codes on those menus.

For Example: if ACP Application Codes opens the user will see my
example from above:

ACP Application Codes ECM US Canada
(y/n = a checkbox)

ACP100 y/n y/n y/n
ACP200 y/n y/n y/n
ACP300 y/n y/n y/n
ACP400 y/n y/n y/n
and so on..............

Each user is allowed to select ACP100 ECM, US, or Canada if they need.
Or they may just need to check ACP US and none of the others. So yes, a
user is allowed to select from more than one application code.

I set up a database with this so far:

tblSecurityRequestIDs
- this table holds all my primary keys for each table I created
below
tblEmployee
- this table holds all the employee information such as name,
title, pay number, start date, requested by and so one
tblRegions
- this table has 5 fields
- REGID *
- SRID (foreign key to tblSecurityRequestIDs)
-ECM
- US
- Canada
tblMainMenuOptions
- this table consists of 40 fields (one for each option on the Main
Menu, MMOID* and SRID)
tblApplications
- this table consists of 21 fields (one for each application, APPID*,
and SRID)
tblLogProDept
- this table consists of 21 fields (one for each department, LPID*,
and SRID)

* = Primary Keys

Then I have 19 other tables which are all named tbl and their app code:
tblACP, tblACR, tblBIL, tblBOM, tblCAP, tblCST, tblDRP, tblFOR, tblINV,
tblJIT, tblMDM, tblMFS, tblMPS, tblMRP, tblORD, tblPUR, tblSAL, tblSFC,
and tblZZZ

Each of these tables has a primary key of its application code name and
the word ID: ACPID, ACRIS, BILID, BOMID, and so on and a field for each
of its selections.

whew..................yeah I know lots of tables.

I have one to many relationships going from my main table:
tblSecurityRequestIDs to all my other tables. Is this right so far b/c
I feel I may be onto something here?

But........my next question is if I have a separate tblRegion how will
I be able to produce a form like the one above in which a user puts a
check in ACP100 - US and the table shows under ACP100 that it is region
US?

Just to make you happy ......of the tables I have set up only Main Menu
Options and Regions are of yes/no data types. Each other table I kept
as text data type. Is this correct?

Well I have babbled about my database enough. Please let me know if I
am onto the right track.

Thanks,
Justine