Data Modeling Question
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)
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)
tblExpense (electricity only)
tblExpense (telephone only)
tblExpense (auto ins only)
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?!
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?! **
Click Here to Expand Forum to Full Width