If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design Question
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! |
#2
|
|||
|
|||
Table Design Question
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. -- Ted |
#3
|
|||
|
|||
Table Design Question
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. |
#4
|
|||
|
|||
Table Design Question
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???? "TedMi" wrote: 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. -- Ted |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table Design Question | Karen | Database Design | 6 | November 16th, 2005 10:50 PM |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
Table design question | CS | New Users | 6 | June 1st, 2005 06:50 AM |
Table and Relationship design problem | douglas jones | Database Design | 2 | March 16th, 2005 11:45 PM |
Table design question - advice needed | David | Database Design | 3 | June 8th, 2004 02:21 AM |