Hello, I am new here and not sure if this is the correct forum to post this.
This is a Data Modeling question.
It is for a personal finance system I've been wanting to build for the past 5 years. My goal is to develop a platform-independent solution, although I'll likely use PHP/MySQL for implementation.
(BTW, I chose to post in an Oracle group since I think Oracle Developers are some of the smartest and most enterprise-focused around!)
The gist of my system is (1) Expense has (m) ExpenseDetails, where "expenses" represent anything I spend money on, including Groceries, Clothing, Fuel, Utilities, Rent, Medical, Travel, etc.
Experience shows me that regardless of the "category", most receipts/expenses have these fields...
tblExpense (all receipts)
---------------------------
ExpenseID
ExpenseDate
MerchantName
ExpenseTotal
PaymentInfo
However, a problem arises - from a data modeling standpoint - with "Utilities", "Travel-related", "Insurance" and other types of expenses.
For these "categories"/"sub-categories", there are lots of other important fields that should be captured under tblExpense, for example...
tblExpense (fuel only)
----------------------
FuelType
TotalGallons
CostPerGallon
tblExpense (electricity only)
----------------------------
DeliveredTo
RateClass
MeterReading_Start
MeterReading_End
CostPerKWH
tblExpense (telephone only)
------------------------------
BillingStartDate
BillingEndDate
TotalLocalCharges
TotalLongDistanceCharges
tblExpense (auto ins only)
----------------------------
BillingStartDate
BillingEndDate
CoverageType
InstallmentFee
LawInforcementSurcharge
and the list goes on...
(While some people might try to cram this disparate data into generic fields, that would be the WRONG approach. A "Quantity" of 5 Apples is NOT the same thing as buying 5.27 Gallons of Gas!!)
While hard to show in just text, conceptually this is what I believe is know a Super-Type/Sub-Type problem. And unfortunately, simplier databases like MS Access - which I'm using to prototype this!! - aren't designed to easily handle this Data Construct. :(
How to handle this predicament?! :confused:
SCENARIO #1: If I create on monster tblExpense and include all fields, then I solve the issue of missing any important information that needs to be captured, however, I will ultimately have a large table with lots of empty cells.
SCENARIO #2: I could create a (super-type) tblExpense with a primary key "ExpenseID" and the common fields described earlier. Then I could create (sub-type) tables, e.g. tblFuel, tblElectric, tblTelephone, and tblAutoInsurance which also have a primary key "ExpenseID" and the additional respective fields in each. Then I suppose I could somehow manage the PK's and synch everything up?! ??? (I believe this is one reasonable path to follow, but I would definitely need "hand-holding" to properly implement this!)
SCENARIO #3: One "un-informed" (and rather pompous) MS Access know-it-all I was being lectured by said I needed to create a tblExpense, tblExpenseAttribute, and tblExpenseAttributeType which would form a M-to-M relationship. After some thought, this might logically work, but I think it goes for "logical eloquence" OVER "implementation practicality". (Remember, I have to build forms and queries and logic to support my back-end design. And, to me, it would be very confusing to store things like "FuelType", "MeterReading_Start", "TotalLocalCharges", and "CoverageType" all in one table as his model would demand.
SCENARIO #4: Be a wimp, and build a seperate system for each Expense-Type because what I am capturing is too disparate and therefore should have its own database/home.
SCENARIO #5: Stop being so "anal-retentive" and just capture "ExpenseID", "ExpenseDate", "TotalAmount" and "Category" and be happy!
===================================================================
**NOTE: A similar problem exists with tblExpenseDetails!!
The data seems to fall into Retail and Non-Retail buckets.
With the first, you will find classic classroom "Order Details" type fields... "OrderItemDescirption", "UnitPrice", "Quantity", etc.
With the second group - which is usually a Utility - you won't find ExpenseDetails because you don't buy Gas a gallon-at-a-time, or insurance in seperate parts. And while there might be Order SubCategories like "TotalLocalService" and "TotalLongDistance", in the end, everything relates back to tblExpense - and not tblExpenseDetail - because you are buying the product/service in totality - usually for the month - if you can follow that?!
===================================================================
In closing, what seemed like a very straight-forward system to build, is actually much more complicated when you look at the "big picture". At the same time, this isn't rocket science, and I am CERTAIN that I can build an intelligent, detailed, robust, and scalable system that meets MY NEEDS if I can just get a little help on the Data Modeling portion! ;D
** Hell of a first post, eh?! ** :D
Sincerely,
Just Bob
