A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
So if i am selling product A (PA), service A(SA) and Gift Voucher A(GA)
I would have on my invoice Item 1 = PA, Item 2 = SA, and Item 3 = GA.
i dont know where to record this purchase transaction, in a table called transactions, and if so how is that assoaiated with the 3(maby more) different types of items
or do i only record payments made on the invoice in transactions and then have each item type track sales via a many to many relationship
should i use a table called items and associate them with invoices/transactions and products?
Thursday, October 26, 2006
Speaking personally I would have two or three tables here
You should have either a record of each voucher issued, or an algorithm for calculating that the voucher code is genuine.
When the voucher is redeemed, then you record the fact.
One for the transaction recording
---id number for transaction
---who it was from
---link to delivery address
---Flag for has this transaction been processed
Once the transaction as created then the items and Services cans be listed in table number two (e.g. transaction_details) with
---an id number for each entry
---a code for Item or SERVICE
---the id number for the look up of your item or service
---either the voucher code, or the id of the voucher on your voucher table if you use one
Thus your transaction table can pull in personal details, date, method of payment and items from the accociated tables.
But that's possibly an old fashioed way of thinking. Interesting to see what others suggest
The classification type of the product belongs in your products table.
When you sell something, you have the transaction, and the product number/id (or however you do this) will be stored in this transaction table.
However, you do NOT need to store the product classification type with each transaction, since you can join you way back to the products table to get the classification type.
Not only do you eliminate the repeating data (so, this is more normalized), but if you accident had classified a toaster as a service, and it should have been a product, then you can simply change this setting in ONE PLACE (that would be in the products table). When change this classification, then all of your reports etc. would reflect this change, and no “update” of existing data would need to occur to fix this mistake. So, keep the product classification in one place, and ONE instance of it will be stored.
Further, by having the classification in the products table, you can add new class types over time (and, of course, really, we would make a classification type table also!!
Albert D. Kallal
Edmonton, Alberta Canada
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz