Hi!
I would be very grateful for your help regarding my issue:
I have Expense Tracking Database. Main Table is list of Expenses with value of transactions:
tblExpenses
For a lot of expenses in this main table (but not all - that is important) I want to add STRICTLY ONE-TO-ONE "properties" or "attributes" like:
Thank you for any help! I'm still learning how to play with Access and I'm not a database expert...
David
I would be very grateful for your help regarding my issue:
I have Expense Tracking Database. Main Table is list of Expenses with value of transactions:
tblExpenses
ExpenseID (PK) | CategoryID (FK) | Note (text) | Value (Currency) |
1 | 1 | Bla bla bla | -256 USD |
For a lot of expenses in this main table (but not all - that is important) I want to add STRICTLY ONE-TO-ONE "properties" or "attributes" like:
- It is Work expense and thus belongs to some specific Work Expense Claim
- It is Regular expense and thus belongs to some specific Regular Expense Pack
- It is Grant/Donation (like some institution provided money, but I still want to keep categorization of that expense, so create special category for this is not the solution)
- It is a Loan and thus belongs to specific Loan Label
- etc.
- Yes, it is possible and quite frequent that there may be combinations of all provided examples (like it is Work Expense AND Regular Expense AND Donation)
- The problem I have is, that every property is relatively rare in comparison to overall amount of Expenses in main table. So to put this all to one table using foreign keys will cause a lot of nulls. So this solution is not making me happy
- Possible one-to-many table (like "tblAttributes") with composite primary keys: [ExpenseID] from tblExpenses and second [PropertyID] from "tblAttributeIDs" so that only one unique type can be assigned to one ExpenseID. Also, in this case I have to put all names of Regular Payments, Expense Claims, etc. to one table and I have worries about data validity (I would restrict use for just one type)
- Create one-to-one table for each type and link them to particular Fact tables containing Attribute names (so in provided example it means extra 8 tables).
- Create one-to-one relatioship with special "attribute table" (example below) with multiple columns representing provided Attribute Types. This will reduce number of nulls, but still a lot of nulls will remain:
ExpenseID (FK) | WorkExpenseID (FK) | RegularExpenseID (FK) | GrantID (FK) | LoanID (FK |
1 | 1 | 1 | null | null |
Thank you for any help! I'm still learning how to play with Access and I'm not a database expert...
David