Re: Database set up help

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 here:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

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

.

Relevant Pages

  • Database set up help
    ... I am starting to wonder if my database is set up incorrectly. ... h two tables: Employee and Termination. ... my bosses wanted me to add Security Options to the picture. ... Like I stated earlier there are 20 application codes and each one has ...
    (microsoft.public.access.gettingstarted)
  • Re: Only displaying the form window
    ... This database has a startup ... I was puting the codes ... > startup form and just use the main switchboard, ... I don't have Access 2003 installed, but if its version of the Northwind ...
    (microsoft.public.access.forms)
  • Re: Comparing contents of two spreadsheets and outputting results to a
    ... is in Sheet1, zip codes in col A from row2 down ... > I have the following - a large contact database is XLS format with address ...
    (microsoft.public.excel.worksheet.functions)
  • radius search
    ... The hard part is your database, which will need to include lat/lon ... you're looking for US zip codes, of course...most countries use a term ... Google Maps Javascript API to do the initial lat/lon calculations, ...
    (microsoft.public.dotnet.languages.csharp)
  • IIS 6 and SQL 2003 sp4
    ... databases in SQL, one called Employees and the other Postal Codes (a.k.a. ... what is the best way to configure a database to be ...
    (microsoft.public.sqlserver.setup)
Loading