Question about working with data from a table ( 6 Views )

1. Hello, I have a basic Mysql question.
   
   I have a table that tracks orders from affiliates. however, it tracks
   different 'packages'.
   
   Each package (multiple products, ordered after one another)
   has a different commission rate
   
   So let's say bob, the affiliate, sells a few for package 3, 5, and 8
   in October, 2003
   
   Package 3, there are 3 sales for $10 product. 50% commission
   Package 5, 3 sales. $20 product. 40% comm.
   "" 8, 3 sales, $30 product, 25% comm.
   
   I'm wondering, how in one mysql query can I find this out? or do I have to do multiple sql queries with an array? It's a different scenerio from what I am used to.
   
   Just wondering what the *least resource intensive* solution is. I am sure I could come up with other solutions (i.e. other tables) but I am not sure it's the best idea.
   
   Just curious as to what you think
   
   Thanks
   
   
   (ömer, Chad)


2. It's my understanding that joins are actually very efficient. So preferred design is to shoot for a normalized structure that allows you to partition data into entities or elements that can stand alone, and then maintain relationships (separate key or ID fields) between the tables.
   
   Once you get the hang of writing queries with multiple joins they're a piece of cake.
   
   
   (ali, Oman)


3. Quote:

   Originally Posted by jbh Each package (multiple products, ordered after one another) has a different commission rate ... Package 3, there are 3 sales for $10 product. 50% commission Package 5, 3 sales. $20 product. 40% comm. "" 8, 3 sales, $30 product, 25% comm.

   Does package 3 always contain 3 products @ $10 each, with a 50% commission to 'Bob'? etc..
   
   It is simply a relationship between packages and commision that you need to find out? Or is the commision variable on how many products are sold (or is the package determined by what products are sold...)
   
   First -- as slider pointed out, I'd begin by normalizing your data: What's a variable? What is constant? Knowing that Bob sold some stuff to customer X, what information do I need to gather how much commision to pay him?
   
   Once you can specify the critieria, post back a bit more information and we can try to help you resolve your DB setup :)
   
   
   (umut, Eritrea)


4. Thanks, guys. My apologies. I just realized the solution to my problem
   
   During the thank you page, all I have to do is just add a field called 'payout' where I simply insert that value. So when I do a query for the month, I can just find the total, from querying the username (affiliate) and the month, and using a sum function to find the overall total payout
   
   Duh lol
   
   
   (oðuzhan, Trinidad and Tobago)


5. I'm also a bit curious about what lieut_data asks. You don't really clue us in to what data structure you're using. If you're just using a single table with large conglomerate records for each "order" then you've got a bad design and you should rethink it. It's easy to see how this design would break by imagining an order with 10 packages, or 100 packages, or 1000 packages. That makes it obvious that you should have a separate table for packages, and a field in that table that lets you know what order each package belongs to. That's what normalization is all about.
   
   Complete normalization isn't always a good thing, and making the decision to store your "payout" value in your database instead of calculating it whenever you need to display it is a perfect instance when that question needs to be examined. The answer may be yes or no depending on what you need/want to optimize. Go for speed and you sacrifice database size. If you're pushing your database limits then you would want to cut fields like this out taking a small performance penalty to do so. Yadda yadda...
   
   
   (@@ebru@@, Qatar)

Related Topics ... (or search in 1.720.883 topics !)