Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2007
    Posts
    20

    Unanswered: Designing wedding database?

    I'm trying to set up a database to manage wedding photography clients, but I am hoping somebody can offer some ideas for how to best set this up.

    When I receive an enquiry about photography, I need to record that in my database with reference to the couple who made the enquiry. Names, address, phone numbers, etc of the couple are needed (in some cases the couple have different addresses), along with the date they enquired, what they were looking for, where they heard about me, and what action was taken.

    Then, some enquiries turn into consultations, but not all do. I need to record consultations where I have met with the couple to discuss my work. Place, time and date are needed here. One couple can have multiple consultations because some meet with me after the wedding.

    Some consultations turn into bookings, but not all do. Some bookings stem immediately from enquiries and skip the consultation process. I need to record the date a confirmation was made, the date the deposit was paid (and the amount), payment method, date of balance installments (and payment method) along with specific information about the package they selected (including a breakdown of components, such as number of hours covered, albums, etc)

    I need to be able to record information about the specific wedding day (separate from the booking section) that details the date, anticipated weather forecast, tide times, etc. I also need to be able to enter an infinite number of wedding locations and their respective details (for example: "Bride's House, 22 Wood St, Scheduled Start Time: 11am, Arrive Time: 10.45am, Departure Time, 12pm, Scheduled End Time: 12.15pm) and then multiple events for each location (if necessary); for example: "Reception, City Hall, Event #1: Cake cutting, start time: 8pm".

    I need to be able to record information about key people on the day: parents of the couple, grandparents, siblings, and bridal party (including names, addresses, relation to the couple, extra notes, role on the day).

    I've tried to set this up many many times, but every time I do I feel like I'm opening Pandora's box and get stuck. Is anybody able to offer ideas for the relationships layout of this database? I'd be very grateful!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Show us what you've tried already and we'll do our best to point you in the right direction!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Posts
    137
    You've definitely set a commendably-sized project for yourself. There's far more to setting up such an application than I can detail for you here, but here's a data model of the tables that might give you a starting point:


    tblClientMaster
    ---------------------
    ClientID (Autonumber)
    GroomName
    GroomAddr
    GroomPhone
    BrideName
    BrideAddr
    BridePhone


    tblKeyPeople
    ------------
    PersonID (Autonumber)
    ClientID (Lookup: tblClientMaster.ClientID)
    PersonName
    PersonAddr
    PersonPhone
    Relationship
    Role
    Notes


    tblEnquiry
    ----------------------
    EnquiryID (Autonumber)
    ClientID (Lookup: tblClientMaster.ClientID)
    EnquiryDate
    EnquiryInfo
    EnquiryReferral
    EnquiryAction


    tblConsultation
    ----------------------
    ConsultID (Autonumber)
    ClientID (Lookup: tblClientMaster.ClientID)
    EnquiryID (Lookup: tblEnquiry.EnquiryID; blank if Enquiry goes straight to booking)
    ConsultDate
    ConsultTime
    ConsultLocation


    tblBooking
    ----------------------
    BookingID (Autonumber)
    ClientID (Lookup: tblClientMaster.ClientID)
    EnquiryID (Lookup: tblEnquiry.EnquiryID; blank if booking comes from consultation)
    ConsultID (Lookup: tblConsultation.ConsultID; blank if booking comes from enquiry)
    ConfirmationDate
    DepositDate
    PaymentMethod
    ...etc.


    tblBookingDate
    --------------
    BookDate
    BookingID (Lookup: tblBooking.BookingID)
    Forecast
    ...etc.


    tblBookingLocation
    ------------------
    LocationID (Autonumber)
    BookingID (Lookup: tblBooking.BookingID)
    Location
    SchStartTime
    ArriveTime
    DepartureTime
    SchEndTime


    tblLocationEvent
    ----------------
    EventID (Autonumber)
    LocationID (Lookup: tblBookingLocation.LocationID)
    EventDescription
    StartTime

  4. #4
    Join Date
    Oct 2007
    Posts
    20
    Thanks for your advice!

    I've had a play with setting this up, but one of my concerns is about the way the database handles Bride and Groom data. I guess I was always taught that different data should be kept separate - would that therefore mean that separate Bride and Groom details should be kept in separate tables? And if so, how would that be best linked to everything else?

    Are there any examples or tutorials or anything like that that might help explain the things you're thinking (of the database being quite complex)?

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    This may sound a little crazy but I believe Microsoft had a wedding planner database as on of its sample mdb's I am not sure which version - but with a little tweaking it might suffice.

    Just a thought - with that said nothing beats designing your own application.

    Matthews structure is very good.
    Dale Houston, TX

  6. #6
    Join Date
    Oct 2007
    Posts
    20
    You are right, Matthew's structure is very good. I hope I didn't sound like an obnoxious twerp in my last reply. It's the best design I've seen and seems to be working well! I was only concerned about the bride/groom issue (I'm database ignorant mind you). I think it's constructing the interface that I'm scared of!

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Well - you are on the right track - and this is what the forum is for - good luck
    Dale Houston, TX

  8. #8
    Join Date
    Feb 2004
    Posts
    137
    As I stated before, there's far more to setting up such an application than I can detail for you here, and it is by no means a simple application to design, so no one can blame you for feeling scared.

    One of the best ways to approach designing the interface is to put it down on paper, what you want to happen, how you want it to happen, etc. Pictures, flowcharts, task lists, etc., are excellent tools with which you can build your physical model around which to construct an application. Having a concrete model can make all the difference in the visualization of a database application's design.

Posting Permissions

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