Table Design Question

[JRB]

I have a table design issue and I'd love some input.....

I have some existing tables which hold data for different business units
(Capital Markets, Investment Sales, Asset Management, Professional Services,
etc.) I also have a table which contains information about employees.

I need to design 2 new tables - one for Time and one for Expenses. Each
Time or Expense record can be related to only one Business Unit (but doesn't
have to be related at all).

I'd like to have 2 tabs on each Business Unit form where the user can enter
the information for time or expenses, and those entries would be
automatically related to the business unit that the user is working on.

Normally, I would just create separate tables for each business unit
(tblTime_CapitalMarkets, tblExpense_CapitalMarkets, tblTime_InvestmentSales,
tblExpense_InvestmentSales, etc.) and that would work fine. However, the
client also wants the ability to enter this information on each employee's
record in which case I don't think this design will work. Is there any way
to create one table for time and one table for expenses that I can use on all
the forms but somehow designate which business unit the time or expense is
related to?

Any help would be greatly appreciated.

Thank you!

 

[TedMi]

Create these tables:
tblBizUnit:
BizUnitID (PK)
BizUnitDesc Text
any other info specific to each Unit

tblTime:
BizUnitID (FK to tblBizUnit)
all other info relative to a time record

tblExpense:
BizUnitID (FK to tblBizUnit)
all other infor relative to an expense record

You can then create a form for entering time or expense for any BizUnit -
that form should include the BizUnitID field that the user can fill in from a
drop-down list of BizUnits. To see time or expenses for a single Unit, write
queries that have the BizUnitID as a parameter.

 

[mnature]

tblTime
TimeID
BusinessUnitID
UserID
TimeQuantity

tblExpenses
ExpensesID
ExpensesName
UserID
TimeQuantity

tblBusinessUnit
BusinessUnitID
BusinessUnitName

In the tblBusinessUnit, have one BusinessUnitName which is "no relation."

You want to automatically link to the business unit the user is working on.
That is easy to do with a table lookup, if the user always works on a
particular business unit. If they switch from unit to unit, depending on
what they are doing, then you have to have them identify the unit each time
they enter time.

Hope this helps.

 

[JRB]

I understand where you're going with this..... but.....

The problem that I'm having with doing it that way is that each business
unit has its own table with data. For example:

tblBorrowers:
BorrowerID
PropertyName
InquiryDate
AssignedTo_EmployeeID
ReferredBy_EmployeeID
Broker_ContactID
Borrower_ContactID
etc.

tblAssetMgmt:
AssetMgmtID
InquiryDate
ProjectName
ProjectDesc
etc.

tblProfServ:
ProfServID
InquiryDate
etc.

Does that make sense? And the tables are too dissimilar to combine in to
one table so that I could do it the way that you're suggesting. Each one of
them also has a substantial amount of fields so to combine them and only use
some of the fields for each business unit would make an excessively large
table.

Does that give any more insight into my problem????