Table set up help........

[JNariss]

Hello,

The previous post is located here:
http://groups.google.com/group/micr...d/browse_thread/thread/5e46bdf9c3372d53?hl=en

I would like some help setting up the tables for my database. If
someone could please read the previous post and tell me if I am on the
right track with my ending post I would appreciate it. I have a couple
of other questions regarding setting up my tables however if I am not
onto the right track in the first place then I don't want to begin
setting up anything.

I have a basic setup for tables:


Table 1:
tblSecurityRequest (I am starting to think this table is unnessesary)

Fields:
SRID (Primary Key)
EmpID (Foreign Key to tblEmployee)
MMOID (Foreign Key to tblMainMenuOptions)
LPID (Foreign Key to tblLogProDept)
APPID (Foreign Key to tblApplications)
RegionID (Foreign Key to tblRegion)


Table 2:
tblEmployees

Fields:
EmpID (Primary Key)
Name (text)
Title (text)
Pay Number (text)
Department (text)
Start Date (date)
End Date (date)
Managers Name (text)
Managers Phone (Number)
Location (drop down)
New Hire (y/n)
Change (y/n)


Table 3: tblMainMenuOptions

Fields:
MMOID (Primary Key)
MainMenuOptions (?????? Do I list the Main Menu Options in one field
such as this and enter them on the data sheet view, or do I enter them
individully as fields within this table?????)

Table 4: tblLogProDept

Fields:
LPID (Primary Key)
LogProDept (?????? Do I list the Log Pro Departments in one field such
as this and enter them on the data sheet view or do I enter them
individully as fields within this table?????)

Table 5: tblApplications

Fields:
APPID (Primary Key)
Applications (?????? Do I list the Applications in one field such as
this and enter them on the data sheet view or do I enter them
individully as fields within this table?????)

Table 6: tblRegion

Fields:
RegionID (Primary Key)
Region (?????? Do I list the Applications in one field such as this and
enter them on the data sheet view or do I enter them individully as
fields within this table?????)


I have created other databases but I feel that this one is different
because the people who will use it (once my tables are set up and my
form is created) will be making selections instead of entering text.
The only text they will have to enter is text from tblEmployee. Every
other table is a yes/no because users can choose more than one item.
For example............there are 3 regions that I need to let users
have a choice of: ECM, U.S., and Canada. I have 19 options users can
choose from for Applications. However once they choose an Application,
they have to choose the codes within the application. So where do I put
those codes??? Do I create another table called tblAppCodes and enter
the 19 applications as fields and use the lookup wizard to manually
enter all of there codes?

Thank you kindly,
Justine

 

[Steve Schapel]

Justine,

As I have explained in the previous thread, your table design needs to
have one field for each data entity, and the data entered into this
field as separate records. In your current design, you are
distinguishing data according to which table it is in, or according to
which field it is in, rather than according to which record it is in. I
tried to express this diplomatically before, but now I will try the
blunt approach - WRONG!! If, for example, the data you are recording
involves the entry of multiple Codes for each Application, then you need
a table where each of these "selections" is a separate *record*. The
fields might be:
AppCodeID (PK)
AppID (FK to Applications table)
Code
.... (although I suspect it won't be as simple as that, as there is the
other stuff about Regions and such like to take into account)

But anyway, I suggest you forget the form and such like at the moment,
and get the tables normalised.