SnarkDreams Database User Needs

SnarkDreams Shisha Exports sells hookahs, shisha, and coals. I use a company called Kagi for my credit card processing. Currently, orders are through their web-based order form. I plan to build my own form soon, and pass the order to Kagi half way through the process. (I still need Kagi because I don't want the expense of opening my own merchant account. Though Kagi is currently giving me grief over the sale of tobacco, they are more convenient for my customers than PayPal or CCNow.)

Here is part of the CGI script still under development that records all the products.

#product info
my @smallhookah = ("Small Hookah", 105, 1.5);
my @mediumhookah = ("Medium Hookah", 135, 3);
my @largehookah = ("Large Hookah", 165, 6);

my @regmolass = ("Regular Shisha: Molasses (Plain)", 14, 0.25);
my @regapple = ("Regular Shisha: Apple", 14, 0.25);
my @regstrawb = ("Regular Shisha: Strawberry", 14, 0.25);
my @regmixed = ("Regular Shisha: Mixed Fruit", 14, 0.25);
my @regapricot = ("Regular Shisha: Apricot", 14, 0.25);
my @regmint = ("Regular Shisha: Mint", 14, 0.25);
my @reglemon = ("Regular Shisha: Lemon", 17, 0.25);
my @regorange = ("Regular Shisha: Orange", 17, 0.25);
my @regcoffee = ("Regular Shisha: Coffee/Cappuccino", 17, 0.25);
my @regsffotd = ("Regular Shisha: Flavor of the Day", 17, 0.25);
my @premmolass = ("Premium Shisha: Molasses (Plain)", 18, 0.25);
my @premapple = ("Premium Shisha: Apple", 18, 0.25);
my @premstrawb = ("Premium Shisha: Strawberry", 18, 0.25);
my @premgrape = ("Premium Shisha: Grape", 18, 0.25);
my @premrose = ("Premium Shisha: Rose", 18, 0.25);
my @lightapple = ("Light Shisha: Apple", 29, 0.5);
my @lightstrawb = ("Light Shisha: Strawberry", 29, 0.5);
my @lightrose = ("Light Shisha: Rose", 29, 0.5);

my @roundcoals = ("100 Round Coals", 25, 0.625);
my @squarecoals = ("20 Square Coals", 5, 0.125);

my @surcharge = ("Shipping surcharge for orders under 3/4kg", 8, "--");

In each array, the first item is the product name, the second item is the price in US dollars, and the last item is the weight in kilograms.

When I get an order, I (and my customer) receive an email like this from Kagi:

Payment fee received:
1 Quantity Payment for Medium_Hookah, US 135
1 Quantity Payment for Regular_Tobacco:_Mint, US 14
1 Quantity Payment for Premium_Tobacco:_Apple, US 18
1 Quantity Payment for Box_of_100_round_coals, US 25

Registered-To: Bob Smith [name changed]
Email: [email removed]@aol.com
Postal: 999 North Doheny Drive #1108, West Hollywood CA 90069, United States of America
Total Amount Paid: USD 192
Date: 2001-07-21
Processed: 2001-07-21
PaymentMethod: Card
Invoice: I366779767
TransactionID: CHW4EHCPDZ2R
Route: S6A5T0R1
Comment: (310) 273-7570
Note: SSL from 172.144.24.31 via KOOP 2.3en for vendor MZC.

Thank you for using Kagi to make your purchase. 
Your order was processed by the Kagi store...

[blurb deleted] 

SQNM-snarkdreams 0071

WO-TFYP 1.6b

[I have removed some of the identifying information.] I disregard all info after Total Amount Paid, except for Comment, which should be the telephone number, and "SQNM-snarkdreams 00##", where ## is the Kagi order number.

I record orders in a little blue book, like so:

[Again, names have been blurred to preserve some small amount of customer anonymity.]

You have now seen the sorts of information I record. I will need at least the following information.

• SnarkDreams order number. The number in the top, outside corner of each page is my order number. Every order has a unique number; an order is equal to a single shipment.
• The date the order came in.
• Kagi order number. The numbers near the inside of the book are the Kagi order numbers. Sometimes there are more than one of these to an order. Sometimes there aren't any if the customer pays me directly by check or cash.
• Customer's name. I want it separated into first and last. Any middle names or initials will go in with first.
• Address. I don't really care about cities, postal codes, or regions, so I want this to contain everything in one field except the country.
• Country. I do care which country orders go to. Also, if shipping to the US, I want to know which state.
• Telephone. I need this for shipping. Sometimes it's really long; I just copy down what they give me.
• Email.
• What they ordered--which items and how many. This should add up to total price. (Currently, orders should be >= 0.75 kg or else have a shipping charge.)
• Total price paid (on the left).
• Kagi's percentage. This is derived from the total amount paid. It's the dollar amount listed on the right. (This is empty if the order didn't come through Kagi.)
• Costs. Already includes both purchasing and shipping.
• Cost subtotal. This is the total it cost me to fill the order.
• Profit. This is the underlined amount at the bottom. It varies rather widely; sometimes it's negative.
• Comments. I frequently write myself notes about an order--odd occurrences, explanations, customer preferences, etc. Should be long enough for two or three sentences.

Questions & Answers/Further clarifications

What are the Invoice, TransactionID, and Route numbers for?

Invoice, TransactionID, and Route numbers are esoteric things Kagi uses. I don't know exactly what they do; I never use them in my business.

Can I recommend you track your inventory in the database?

I don't keep an inventory. By shipping directly from Abu Dhabi, UAE through a purchasing agent, I can fill orders as they come in without the hassle or cost of storing a standing inventory.

Is "profit" a derived attribute?

Yes,. In some models, such as UML, there are special places for methods and derived attributes. In others, they can just be treated as normal attributes. It will make a difference when it comes time to implement that it is derived. But for now, you can just treat it normally in most cases.