Basics 1

[goslincm]

I keep struggling with table structure after reading and studying various books, articles, etc. Makes sense when I read it but when I go to apply its another story.

Example: I want to create a database to track travel expenses. So when an employee audits a business during the month I want to track the expenses for that audit, AND I want to have more than 1 audit allowed to be submitted on the same expense report for a given month. I can't get this set up right.

So, I have employees who audits businesses incurring travel expenses along the way. I create:

tbl_Employee (emp_id, emp_lname, emp_fname)
tbl_BusinessName (business_id, business_county, business_name)
tbl_Expense (expense_id, exp_gas, exp_lodging, exp_meals)
tbl_AuditType (audit_id, type)

I don't know how to set this up...

Can anyone take the time to walk me through this?

 

[Golom]

You appear to have identified the entities involved but you haven't established any relationships between them via foreign key fields.

For example, how do we determine
- which employee incurred a particular expense?
- which audit(s) an employee is working on?
- which audit an expense applies to?
- which business an audit is being done for?

Also ... some business rules
- Can an employee work on more than one audit at a time?
- Is "time" relevant here? That is, could we determine that a particular expense applied to a particular audit because the expense happened during the time interval that the audit was being performed?

Just as a starting point, you might consider

tbl_Employee (Emp_id, emp_lname, emp_fname) 
tbl_BusinessName (Business_id, business_county, business_name)
tbl_Expense (Expense_id, Emp_ID, exp_gas, exp_lodging, exp_meals)
tbl_AuditType (Audit_id, Business_ID, type)
tbl_EmployeeAudit (Audit_ID, Emp_ID)

In this I've assumed that an audit is performed for a single company but more than one employee may be assigned to an audit.

 

[goslincm]

I think I am alright with identifying the entities involved however, like you ask?

business rules: can an employee work on more than one audit at a time? Yes, during a month, an employee will generally work on more than one audit.

Time, would generally run, month to month, and during the time period one to several audits are likely to occur at a particular business. I don't know how to set up those relationships.

 

[Golom]

Time would generally run month to month

Such reporting is impossible with the tables that you have because there are no date/time stamps that would allow you to select a particular month.

Give some thought to business events (e.g. Audit Started, Audit Complete, Expense Incurred). Each of those happens on some specific date and time and that should be recorded in your tables.

You may also gain some insight into your data requirements by doing mock-ups of the required reports and then comparing those to your table layout. You should have the data defined in the tables to contain the required report fields or at least to compute values for them.

 

[goslincm]

Golom-thank you for your help today. Tomorrow I will continue working on this under your direction. Thanks again.