Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003

    help me plan my order management system, please!

    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.


  2. #2
    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

  3. #3
    Join Date
    Jun 2003
    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.


  4. #4
    Join Date
    May 2005
    Los angeles, CA
    Sorry, I know this is off topic, but I'm quite curious about your PHP commerce thing you wrote.

    So Rad, you made this, and then how exactly did it work? Just some forms that were filled out, and then written to some file on the disk?

    Any encryption? Have you tested it, and does it work well?

  5. #5
    Join Date
    Jun 2003
    I've been recently working on a site for a customer who has hmm... around 150-200 total items they wish to sell.

    These items can be split into about 3 catagories:

    Stuff that can be customized with A
    Stuff that canbe customized with A & B
    Stuff that can be added directly without customization

    cart.php determines which type of "stuff" it is and adds it to the cart by adding it to a $_SESSION array. As long as the session last, the cart can be printed out accordingly.

    So my session array consists of something like this:

    'item ID','item name','item price','item long description'
    'item ID','item name','item price','item long description'
    'item ID','item name','item price','item long description'

    and so on... for as many items as they want to "add to cart" (add to array)

    When they checkout, hidden inputs are created and send to a form email, which then sends the form email to my Client.

    It could be easily altered to work with a credit card system, and SSL could be added, but it's not needed with my client.

    That make any sense? =P


  6. #6
    Join Date
    Jun 2004
    Bay Area -USA
    Just an additional piece of information:

    To test your paypal stuff you can register for a paypal sandbox account which allows you to create fake accounts for testing purposes.

    With this you can access a lot of the information like what the user paid and stuff and incorporate it into your script pretty easily.

    If you need help with the paypal stuff PM me cause I've been working with it for a lil while.

    There is a paypal class I have which makes setting up the whole paypal instant payment notification really easy to deal with.

    Good luck with your product!
    <<< Please see Forum Guidelines for signature setup. >>>

  7. #7
    Join Date
    Jun 2003
    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!)

  8. #8
    Join Date
    Jul 2003
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts