I'm trying to put together a basic web-based order management system. Let me explain the needs that we have:
1. We will be selling many copies of 2-3 CDs via Paypal on our website, taking mail-in orders, and do some transactions in person.
2. There are 3 people who need write access to this system. All 3 of them for entering in orders as they are received, one of them for processing/shipping orders, and one other who takes care of financials.
There's really no need for a full-blown shopping cart (i.e. OSCommerce, etc) for this situation, I don't think, because of the size of the operation. Customers don't need to be able to see the status of their orders or have 'accounts' or anything like that. I simply am looking for a solution that allows 3 people to keep track of sales and do post-sales things like figuring out sales tax payments to the state, and keep track of order status (received, processing, shipped) all in one central location (hence the need for a web-bases system).
I was thinking I'd set something up in PHP/MySQL to take care of all this. Something that has 4 tables:
customer - contains customer name, address, etc
recordings - contains title and cost of each different CD
orders - contains order date, status, method of sale (in person, paypal, mail, etc)
items - contains an item, quantity, price
I've never set something up like this before, and I'm not really sure how to make it all work together. In the past, I probably would have setup on table that contained all the info, inputting data from a single form on one page. That would have simplified it in some respects, but as I'm learning, setting up more than one table has a lot of distinct advantages with a relational database system, and allows for growth in a way that the single table model would not.
I would love to learn how to get all this to work together, but I feel like I just don't quite have a good enough grasp on it. Any pointers in terms of the DB design or in how this process can work (how many pages/forms I should use to get the data into the DB) would be most helpful. I want to learn, but I'm just not able to wrap my mind around all of it just yet.
You are right that setting up more than 1 table has advantages in relational database design. I studied database normalization theory when I was earning my CS degree. Let me tell you, it is not trivial. My honest advise to you is get a professional to do what you need. You will need to pay them, almost for sure, but you will be a lot happier in the end when your system keeps churning orders for many years to come. The trouble of trying to do something like this yourself is that the naive design usually locks up 6 months down the road (we call it "not scalable").
██ Garry Dolley @ ARP Networks | gdolley _at_ arpnetworks . com | #arpnetworks on Freenode | @arpnetworks, @bsdvps
██ State of the art services, strong community and friendly support
██ FreeBSD, OpenBSD, Linux VPS ARP Metal Dedicated Servers Colocation IP Transit
██ Native IPv6 VNC Console Tunnel VNC over SSH Serial Console over SSH DNS Manager
I just recently made my own "cart" so to speak(pure php, no sql), so this seems really interesting to me.
Just wondering though...
Paypal sends both you and the customer an email recepit (no need for sql table)
Mail in orders wouldn't be done through the website (no need for sql table, unless you mean form email, in which case you still don't need sql)
Obviously transactions in person don't need a database.
So, if what you need is a database to keep track of orders placed after they've been placed (such as... your helpers type in stuff which in turn enters into the sql database), then why not save the hastle of hiring someone and just keep it all on paper?
And just to look deeper and figure out what you're asking for in terms of tables:
customer - contains customer name, address, etc (you could easily set up a php array for your helpers to hand code as they came in, which could then be displayed on a private page to view all)
recordings - contains title and cost of each different CD (php array, easy)
orders - contains order date, status, method of sale (in person, paypal, mail, etc) (again, php array like the "customer" array)
items - contains an item, quantity, price (same thing as "recordings")
If you have 3 people adding stuff in, and they know the slightest bit of php code (or have 10 minutes for you to teach them), each of them could have a seperate .php in which they add more elements to their page's array... then have one page include them all and you have your entire customer base.
In other words, I don't see why sql needs to be used in this case if your helpers are manually typing them in anyway =P
If these 3 people are NOT manually entering data, then you may run into trouble using paypal when automatically submitting the data to an sql database.
the first problem I see with paypal (with my limited use), is when do you feel confident enough to send variables to a database? Certainly you can't do it when they "continue" the paypal buy process, because they can back out. And if you do it when they're sent to the return page, it would be easy to screw you up by simply going to that page without going through the payment process.
Is this concern reasonable?
(don't mean to hijack the thread, hope this kind of question is of interest to you too!)
Paypal's return process protects you from such things.
First, they return transaction codes, so you can see if a request is coming from Paypal, or someone is just goofing with your script. A lot more information (with examples) is available at Merchant Tools section in Paypal, including the IPN manual which is downloadable from there.
As for the original post -- you need to decide what information you want to store, and write this down. Start with the very basics.
For example, you want to store information about 'Customers', 'Transactions' and 'Products'.
Now, you take each item and add details to what you would like to store about each item. So, as an example, lets take 'Customer'. You would like a few details, such as their name, addresses, purchase history.
Now, you break down this information into more concrete elements -- in other words, what makes up a record for a customer? For example "name". Would you like to store the name as one item, or would you like to store the first and last name individually? Do the same for the rest. For example, address. Do you want to store the entire address as one 'element', or different components (street, zip, country).
By the way, what you are doing above is determining what atomic value you want to store. Its not always necessary (depends on the application) to store each item in its most atomic state. For example, if you were running a email campaign, you would want to store the domain of an email address as a separate entity so you can send query your database faster for each domain. So if you wanted to send emails to addresses of each domain, your application would respond quickly. You can find out more about this stuff in any database design book, but I recommend 'Database Design for Mere Mortals'.
After a few sessions of PaperWare, you will have the layout of your customer table. Here is a rough sketch :
customer_id (something to uniquely identify each customer record)
Hopefully this gets you started, but you should really consult someone that knows about database design -- or buy that book, read it (twice) and start it on your own
In order to understand recursion, one must first understand recursion.
If you feel like it, you can read my blog
Signal > Noise