Database Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community ForumsDatabasesDatabase Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old July 8th, 2004, 10:41 PM
DanBaker DanBaker is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 3 DanBaker User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question Order & Fullfillment DB Design

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

Reply With Quote
  #2  
Old July 9th, 2004, 07:13 AM
dhouston's Avatar
dhouston dhouston is offline
Contributing User
Dev Articles Beginner (1000 - 1499 posts)
 
Join Date: May 2003
Location: Tennessee
Posts: 1,355 dhouston User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 7
Send a message via ICQ to dhouston
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.

Reply With Quote
  #3  
Old July 9th, 2004, 10:34 AM
DanBaker DanBaker is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2004
Posts: 3 DanBaker User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Thumbs up

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

Reply With Quote
  #4  
Old July 9th, 2004, 12:50 PM
dhouston's Avatar
dhouston dhouston is offline
Contributing User
Dev Articles Beginner (1000 - 1499 posts)
 
Join Date: May 2003
Location: Tennessee
Posts: 1,355 dhouston User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 7
Send a message via ICQ to dhouston
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.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesDatabase Development > Order & Fullfillment DB Design


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway
Stay green...Green IT