|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
I'm creating a database to handle and track orders and the fullfillment of those orders.
My current design has about 12 tables. I have a couple of issues that I would like some advice on. Issue #1: In the past, I've had a problem with "things changing". Example: A table "Items" which contains the Name of the item, and the Cost of the item. A table "PurchaseOrder" contains links to "Items" that were purchased. I never delete any records from the PurchaseOrder table, so it can be used as a history to track who bought what. The problem happens when the price for an item changes. All the history of that item now has the new price, not the actual price they paid for the item. I have two ideas for solving this problem: Idea 1: don't allow anyone to change the price of any items. Force them to create a new item. (I don't like this idea) Idea 2: Since the relationship between "Item" and "PurchaseOrder" is a N-to-N, an intermediate table needs to be created. I called this table "BillItem", and it contains Item_ID and PurchaseOrder_ID (duh). I added another field called "ActualCost" to this "BillItem" table, which reflects the cost at the time of purchase. Comments? Issue #2: The company I'm working for is looking into selling services, which clients can signup for and pay monthly or yearly. The clients get a discount if they pay yearly. Anyway, I need some method of knowing what clients have signed up for what services. And, what clients have already paid for what services. I'm curious about how I should flag the "Monthly-or-Yearly" (use an enum?) My plan is to create a table called "Recurring", which has a link to the service which the client wants. I will use this table to create new PurchaseOrder records every month to pay for the monthly service. This database design seems like it would be a fairly common database in these days of the internet. Does anyone have any comments on what I've done? Things to do? Thing to NOT do? Thank you, Dan Baker PS -- Some of my current tables are as follows: List_Items: Name, Price List_Service: Name, MonthlyPrice, YearlyPrice List_Operator: Name, PhoneExtension List_Salesman: Name, PhoneExtension Client: Name, Address, Phone PurchaseOrder: Tracking#, OrderDate, FullfillDate, List_Salesman_ID, List_Operator_ID, Client_ID BillItem: ActualCost, PurchaseOrder_ID, List_Items_ID BillService: ActualCost, PurchaseOrder_ID, List_Service_ID Recurring: RenewalDate, IsMonthly, Client_ID, List_Service_ID |
|
#2
|
||||
|
||||
|
Is there any reason you couldn't just list the actual price in the PurchaseOrder table? Since it's an archival table anyway, I don't see any reason to split the data out. Once an order's placed, just copy all the relevant, otherwise mutable data into this table.
Regarding yearly/monthly services, you could use an enum, but I'd be more likely to use a lookup table that would contain a unique id and a label for each type of payment frequency. You'd store the id in the purchase order. Advantages include flexibility (what if your boss wants you to add quarterly billing later?) and transparency (it's easier to see and document a lookup table than an enum field). It's also easier to build a select box from a lookup table than from the values of an enum field, which you'd either have to figure out how to look up or you'd have to hard-code somewhere.
__________________
Please don't PM me asking for solutions outside the scope of a thread. Keeping all responses in a thread stands to help others who come along later, which is after all what this forum's all about. |
|
#3
|
|||
|
|||
|
The reason I can't put the price in the PurchaseOrder, is because there can be multiple Items & Services per PurchaseOrder.
I do plan on having a field in the PurchaseOrder table that reflects how the purchase was paid for (check# or last few digits of the Credit card, etc). I'm curious you mentioned "copy all the relevant, otherwise mutable data" ... what does "mutable data" mean? The idea of making a table for "Monthly" or "Yearly" (maybe Quarterly) is fantastic. Thank you! DanB |
|
#4
|
||||
|
||||
|
Heh, I don't know where I got off phrasing it "mutable data." What I means was that, as you originally noted, item prices can change; so this is mutable (changeable) data that you can't rely on for a lookup from a purchase order table. You therefore need to copy the current item cost somewhere to keep a record of the item's cost at the time of the order. If the PurchaseOrder table is just a set of references to items and you therefore can't copy item data to it, you might consider copying item data to another table that the PurchaseOrder table actually points to. Fields in this table would include order_id, item_id, cost (copied from item table at time of purchase), and any other data that stands to change over time but needs to be frozen for the given order.
|
![]() |
| Viewing: Dev Articles Community Forums > Databases > Database Development > Order & Fullfillment DB Design |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|