10 Replies Latest reply on Nov 1, 2011 7:45 AM by mgores

    VERY excited, yet.....

    RainShyneMorpheus

      Title

      VERY excited, yet.....

      Post

      OVERWHELMED!!!

      I'm a Passion Parties Independent Consultant, and I'm really happy to have found this program!  I did the entire tutorial, so I understand the basics, but I'm not sure how to set up my database!  Can anyone help?  I know the info that I have, and I know the "reports" that I want to run, but i have NO IDEA what the best way to enter the data is!  Also, I want to create a catalogue of all the items in our catalogue, but do I have to do that in a different database?  Oy.

      INFO I HAVE:

       

      Client name, address, phone #s, email

      Date of the party

      Hostess of the party (if the person was a hostess, i need additional info to pop up)

      ADDITIONAL INFO

      This party booked from (previous hostess' name)

      Party amount

      Hostess Rewards (i have a list of different ones)

      Hostess Credits (if these could populate in the form that'd be great)

      Hostess Discounts (if these could populate in the form that'd be great)

      Number of parties booked (and their names & ph#)

      Items the client purchased (item #, product name, quantity, price)

      Items rec'd at the party

      Items to be shipped

      Merch total

      Shipping

      Taxes (12%)

      Subtotal

      Less hostess credit

      Less discount

      Final total

      Method of payment (cash, cheque, debit, Visa, MC)

      Credit card#

      Expiry date

      CVC Code

      Signature received

      Date last called

       

      I need to be able to find all this data in many different ways, so here we go:

       

      I NEED TO BE ABLE TO:

       

      Bring up a list of client names and the last time I called them

      Bring up a single client and see her entire purchase history (just the items she purchased), the last party I saw her at (meaning she made a purchase at that party), every party I saw her at, the first party I met her at, whether or not she's booked a party, and if so, the date of that party

      Bring up a list of which items i've sold, how many, and to who (meaning when i click on or bring up that item, i get a list of names of women who've purchased that item)

       

      As you can see, I'm a tad overwhelmed!  I don't know how to create this database!  If someone, anyone, can help me, I would love you forever!!!!!

       

      HALP!!

       

       

        • 1. Re: VERY excited, yet.....
          RainShyneMorpheus

          Wow.  21 views, no replies.  Not even a hint of how i might do this.  Wow.  Little discouraged here; I'd heard this forum was helpful.

          • 2. Re: VERY excited, yet.....
            mgores

            RainShyne,  I would start off with separate tables for contacts (people), parties (events) and Products.  Once you have those set up with the applicable fields, you will need join tables to connect people to parties (for who attended what parties), people to products (who bought what).

            Each of the main tables should have each record serialized i.e. contactID, partyID, ProductID.  The join tables would then contain pairs of those IDs  i.e.  the People_Party table would have contactID and partyID for each person that attended each party.  So you can search for a particular partyID and see all of the attendees of search for a contact and see all of the parties they attended.

            • 3. Re: VERY excited, yet.....
              RainShyneMorpheus

              Ok Mark, so I create 3 tables:

              People

              Parties

              Products

               

              Totally get the ID#s for the people, parties, and products.

              Now will I also be able to bring up a product, and see what my sales history for that is?  Meaning if I look up a bullet, I get a total number of sales, and a list of names of who purchased it?

               

              What about creating a separate table for order forms?  Every person has an order form....

              When I create a Person, I want to be able to bring up their order forms if i want, what they purchased, where i met them and how many parties they've attended and whether or not they've booked.  Are these fields I should be putting in the Person table?

               

              Thanks so much, Mark!

              • 4. Re: VERY excited, yet.....
                mgores

                Your order forms would be a kind of like the joint table between People and Products, similar to the people_parties.  That would would allow you to see how much of each product was sold (to who and when) or what each person bought.  Once you create a new order form, you pick the person from your contact table (or add them if they are new) and pick the products they order from your product table.  As you add each product to the order form you create a new record in the people_product join table that contains the orderID, contactID, the productID and qty.

                So you would have the 3 main tables and 2 join tables (for now)

                Parties>--Party_People--<Contacts>--People_Products--<Products

                • 5. Re: VERY excited, yet.....
                  RainShyneMorpheus

                  Okay, I'm a visual person, and i'm sitting at home with a big ol piece of brown wrapping paper.  If I can visualise what I want to create, I'll be off to the races in a huge way.  What you've described, essentially, does it look like this?

                   

                  I also want to create another catagory called "Booking Blitzes".  This is when I go through my list of clients and give them a call to book a party.  Is my diagram making any sense?

                   

                  I'm thinking if I get the broad things figured out first and get em visualised, then I can actually focus on smaller details, right?

                   

                  What a beast this thing is turning out to be!  Thank you so much for your help!

                  • 6. Re: VERY excited, yet.....
                    mgores

                    Yes, that looks like it will work.  Have been building something similar for my sons' scout troop and it does turn into a beast rapidlly  Smile

                    Am sure you will be wanting to add fields to the different tables as things go and you figure out which pieces of data need to be grouped together.

                    • 7. Re: VERY excited, yet.....
                      RainShyneMorpheus

                      Okay, attached is a Photoshop plan I drew up.  Does it matter which table I create first?

                      I'm having a hard time wrapping my head around how everything is going to work, so should I just create my Products table first?

                      On one Products page, here are all the fields I want to have:

                       

                      Product ID#

                      Release date, disco date

                      In a collection? (if so, list of [catalogue, name, #])

                      In my show? (if so, pt. 1 or pt. 2, which catalogue)

                      Batts needed

                      Picture

                       

                      CATAGORIES (I want the product to be able to be placed into more than 1 catagory if I need)

                      -Bullets & Such

                      -Clean, Tidy, Sexy

                      -Collections

                      -C Rings & Such

                      -Deluxe Toys

                      -Flavored

                      -For Him 

                      -Games & Fantasy

                      -Glass

                      -G Spot

                      -Hostess Rewards

                      -Kegels     

                      -Library

                      -Lubricants

                      -Massage  

                      -RT

                      -Sensual Touch

                      -Toys    

                       

                      PRODUCT ICONS

                      condom compatible, edible, exclusive, paraben-free, silicone, waterproof, vag friendly

                       

                      PURCHASE HISTORY 

                      month, quarter, 6 month, year, all-time

                      who purchased it (list of names)

                       

                      RETURN HISTORY

                      Date of purchase, date of return

                      Client product purchased by

                      Problem with product

                      Date mailed back

                      Tracking number of package

                       

                       

                       

                       

                      WHOO!  Alot, eh?  Any help would be greatly appreciated!

                      • 8. Re: VERY excited, yet.....
                        mgores

                        That looks about right for the basic structure so far.

                        It looks like catagories should be another table as well, since each product can have several catagories and there are multiple products in each catagory.

                        The purchase and return history would also need to be in a separate table, they could probably be combined into one history table with a field to label it as purchase or return.

                        • 9. Re: VERY excited, yet.....
                          RainShyneMorpheus

                          Oh boy.  Okay!

                           

                          In regards to the purchase and return history...

                          When I bring up a product, I want to see if the product has ever been returned.  If one has been returned, then I want a YES "link" to appear so I can click on it and bring up that info on a separate "mini" page.  If the product has never been returned, then I just want it to say "NO".  I also want a button I can click on to bring up a return form.

                          When I am entering info, if I have to return something, then I'll bring that item up, click the return button, and fill out the product return form, yes?

                           

                          This is turning out to be so huge!  But I'm so stoked about it!

                          Maybe I should just start out by creating the CLIENTS table first?  Everything, after all, is kind of based on the CLIENTS table, no?  Basically I'm just trying to start somewhere simple!

                           

                          So for the CATAGORIES and the HISTORY, are those connectors like ORDER FORMS and PEOPLE CALLED, or are they a sub-table of PRODUCTS?

                          • 10. Re: VERY excited, yet.....
                            mgores

                            I would start with the products and catagories tables.  The categories would be another table with a join table between it and products.  Set up a catagoryID and category for the Catagory table.  Set up productID, product name, release date, disco date, catalog name, cat number, show, batt, picture.  The ID fields should be auto entered serial numbers, the date fields should be dates, the picture field a container and the other fields text.

                            Then create another table products-catagories, set up fields productID and catagoryID - both number fields.  In the relationship graph place the product_catagories table between the other two and drag lines from Products::productID to products_catagories::productID   and Catagories::catagoryID to products_catagories::catagoryID.  Double click each of the boxes between the tables and check the "allow creation of records" on the product_catagories side of each relationship.

                            You will then be able to go to a particular product on a product based layout and show all catagories it fits into and vice versa.