Foreign Key

[Lena]

Hi,

I need to build a database for conversion process for CPA company for my
Accounting Information System class.

I have the following tables:

Professional Direct Labor Tablerof. DL Type ID, Description, Standard Cost

Prof. DL Labor-Prof. Direct Labor Operation: Prof. DL Type#, DL Ticket#,
Start Time, End Time, Job ID

Prof. DL OperationL Ticket#, Date, Staff#

Prof. DL Opeartion-Job Costing: DL Ticket #, Job#

Job Costing: Job#, Start Date, End Date, Manager ID


Could you, please,tell me if it is reasonable to post a foreign key "Job ID"
in the table "Prof DLType-Prof. DL Operation"? I need to create a form for a
time ticket where each emloyee can list the time he/she worked on each job
during a day, and it seems to me posting the foreign key "Service Job Id"
into the "Prof DL type-PDL Operation" table is the only way to accomplish
this.

Could you, please, tell me if it's appropriate to do this way. If not,
please, advise how I can organize my tables to create a time sheet form for
each employee to record all work done by him for all types of services. For
exampple, I wnat an employee to be able to record 4 hours spent on auditiing
Company A, and 6 hourse on consulting svc for company B during the same date
on the SAME TIME TICKET, (not two time tickets)




Thanks!

 

[tina]

don't design your tables based on data entry considerations. design and
relate the tables according to relational design principles, to protect data
integrity, and so that you can leverage the power of Access to manipulate
the data as needed for business purposes. my guess is that your class, or
another class you may have taken, gave you some basic lessons in using the
Access software, and perhaps just a little instruction in relational design
(normalization). your posted tables design supports that guess: you have
some idea of relating tables, but don't really understand how or why you're
doing it - otherwise you wouldn't ask the question you asked. recommend you
study up on relational design principles, until you understand the concepts,
then build your database following those rules. even if your instructor is
not impressed with your application, you'll reap the benefit by being miles
ahead of the many people who jump straight into using Access without any
clue how to use it efficiently and effectively. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

and btw, when you're ready to build your tables/relationships according to
relational design principles, suggest you go back to the above link and read
tips 2 thru 9.

hth