justbob3377

But that was the point... I was originally thinking that if I had an Expense (e.g. Haircut) where the Expense = ExpenseDetail, that you could just not create an ExpenseDetail (to save data entry). For example... ExpenseID = 2 ExpenseDate = 7/20/08 MerchantName = "SuperCuts" --------------------------- --------------------------- TotalSale = $20 After talking to someone else, it sounds like that would defeat the purpose of having... (1) Expense ---> (m) ExpenseDetails Follow me? Just Bob

Hopefully this is a clearer description of my question/problem... 1ST PURCHASE: I go to Target and buy a CD, Scotch Tape, Toothpaste, and 5 T-Shirts. There is one "Expense" (i.e. Receipt/Order/Invoice) and there are many (8 actually) "ExpenseDetails" (i.e. CD, Scotch Tape, Toothpaste, and 5 T-Shirts). **BLUE denotes data in "Expense" table **RED denotes table in "ExpenseDetail" table So, in this case I would write... ExpenseID = 1 ExpenseDate = 7/19/08 MerchantName = "Target" --------------------------- Description = "CD" UnitPrice = $10 Quantity = 1 ExtendedPrice = $10 ... Description = "Scotch Tape" UnitPrice = $2 Quantity = 1 ExtendedPrice = $2 ... Description = "Toothpaste" UnitPrice = $4 Quantity = 1 ExtendedPrice = $4 ... Description = "T-Shirt" UnitPrice = $6 Quantity = 5 ExtendedPrice = $30 --------------------------- TotalSale = $46 Straight-forward enough, right? 2ND PURCHASE: I then go to SuperCuts and get a Haircut. There is still one "Expense" (i.e. Receipt/Order/Invoice), but it almost seems pointless to create an "ExpenseDetails" (i.e. 1 Haircut) for this "one-off" deal. Yes, I confess that I am thinking from a Forms standpoint more than a Data/Tables standpoint! On the backend, it is a matter of having... ExpenseID = 2 ExpenseDate = 7/20/08 MerchantName = "SuperCuts" --------------------------- Description = "Haircut" UnitPrice = $20 Quantity = 1 ExtendedPrice = $20 --------------------------- TotalSale = $20 versus just having... ExpenseID = 2 ExpenseDate = 7/20/08 MerchantName = "SuperCuts" --------------------------- --------------------------- TotalSale = $20 I guess my logic was, that in the "abbreviated" version, you really don't have a need for "UnitPrice", "Quantity", and "ExtendedPrice" - among other fields - when you know that you got a Haircut at "SuperCuts" for $20. (And actually, what set this off was for all of my Utility bills. In my mind, all I need to see is "PG&E" and "$120" and I know I have an Electric Bill.) See why I started questioning things? Just Bob

Sorry, I cross-wired my #'s before. Let me start over with a clearer example to follow...

Here is something I've been pining over... Let's say you have a database with an Expense and ExpenseDetail table, respectively. And, let's say that there are two types of data entry forms: Form #1: Expense-level only Form #2: Expense-level + ExpenseDetail-level For most things (e.g. Groceries, Retail Purchases, etc), you would want to use Form #1 because you likely purchase many "Items" for a given trip to the store (i.e. "Expense"). But, for some "one-off" things (e.g. Haircut, Shoe Shine, Gas Pump Receipt) you could use Form #2 to streamline data-entry. (Why enter an ExpenseDetail for a Haircut when you only ever get one Haircut at a time - unless you have multiple heads!!) Now for the question... While such a design wouldn't break any Data Modeling Rules - as far as I know - it could cause conflicts when you query data!! For instance, if you have a total of 10 purchases, and all 10 have Expense-level data, but only 6 have Detail-level data, then if you queried table ExpenseDetail to find "Total Expenditures" you would get incorrect results. (If you queried "Total Purchase Amount" in table Expense, however, you would "catch" all 10 purchases and find "Total Expenditures".) Is this a problem? Can a person just be mindful of this when you create queries/reports, or is this a "Design Flaw"?? Just Bob

Thanks for the reply! I will likely have 10-15 different forms which will be pretty diverse (e.g. Groceries, Fuel, Electric, Car Insurance, Telephone, Airfare, Medical, etc.). And they will be diverse enough to where you wouldn't just want to hide/unhide a few fields. They need to be completely different creatures, although to the user, I want the user to feel like they are on just one "Expense Data Entry Form" and the form adjusts to what they are entering. Conceptually, do I want to create just one HTML/CSS page and use code to dynamically alter which fields are displayed. Or, do I want to do like I think they did in the "old days" and have one "SuperType" main form, and then create, say, 15 "SubType" Sub-Forms and depending on the Expense Type, display the applicable Sub-Form in the Main Form? (I believe that is how people used Frames in the past, although I hear Frames are evil and I want to avoid them!) Also, what would be creating the different Sub-Forms and/or Fields? Is that something PHP does? AJAX? CSS? Pardon the dumb questions, but while I know what I want/need, I really know very little about web design. (I used to know HTML and CSS, but that was like 4 years ago. I have never worked with PHP or AJAX, although I want to start!) Just Bob

Not sure where to post this?! In my database model, I have a SuperType (tblExpense) and several related SubTypes (tblFuel, tblElectric, tblTelephone). On the data entry form I want to build, users will always see "ExpenseID", "ExpenseDate" and "MerchantName" which are in tblExpense. However, depending on the value selected for "Form Category" and "Form SubCategory", users should see different remaining fields. (The logic being that you should see "Total Gallons" when you are entering in your Electric Bill!!) **Blue fields are always the same, regardless of Expense Type. ** Red fields vary depending on the Expense Type, and are determined by "FormCategory" and "FormSubCategory" the user would select. For example, if you wanted to enter July's Electric bill, you would see... ExpenseID ExpenseDate MerchantName BaseServiceCharge TotalKWH SupplyRate TotalSupplyCharge DeliveryRate TotalDeliveryCharge TotalExpense If you wanted to enter a Gas receipt, you would see... ExpenseID ExpenseDate MerchantName FuelType NumberOfGallons CostPerGallon TotalExpense In the MS Access database I am using to "prototype" this system, I will probably use a Tabbed Subform and VBA code to dynamically display/hide different tabs containing the appropriate fields. However, I am unsure of how to do this on a web-form?! ??? So, how can I create a dynamic "sub-form" which will display different fields based on the Expense you want to enter?? I am pretty flexible - for now - on what the solution looks like. It could be... 1.) A single, homogeneous form where only the fields change 2.) A main form that is static with a sub-form that dynamically changes and displays different fields 3.) Something more sophisticated like a Tabbed Subform that hides/unhides different tabs and/or fields as needed. **I prefer to NOT use Frames! Is this something that you would do using PHP? HTML? CSS? AJAX? More so, how would you do it?! Just Bob

Do you think it's worth trying to build my Expense system with PHP/MySQL or is it more practical to keep "nursing" MS Access for now? If I thought I could get a basic back-end built and with some data entry forms then I'd jump in. Just Bob

Hi. Newbie here. I have had it up to HERE with Microsoft and MS Access and am ready to start using a more intelligent solution... Unfortunately, I have no experience with WAMP. (Have lots of database and programming experience, though.) Questions: -------------- 1.) Realistically, how long would it take me to install Appache (?), PHP and MySQL on a Windows XP machine? 2.) How long will it take to get these configured and working? 3.) If I get a Dummy's book, use online help, and use this website, how long should it take me to have a reasonable backend database and local web-forms working on my computer? I have an Expense database that desperately needs building as I am swamped in Receipts, and I am tired of digging myself deeper with MS Access, however, I don't want to wait 3 months for WAMP to help save me. Any wisdom would greatly be appreciated! Just Bob

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. 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?! ??? 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! ** Hell of a first post, eh?! ** Sincerely, Just Bob