Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Unanswered: Linking customers

    Hi there

    Excuse my ignorance, I need some help regarding linking customers to each other.

    scenario - I operate a wedding video business and am designing a database and have a customers table that individually lists customers. My problem is that in this business 2 customers constitute a couple, so how do i link them?

    Do I just have cust1 and cust2 in same table entry therfore making it a rather "wide" table structure. Alternatively my thought process was that I list the unique details for each customer as seperate entries (i.e. they may be living at seperate addresses, they have different parents,etc) then somehow link them as a couple and then list details unique to the couple (i.e. celebrant, date of wedding, church/location, etc ).

    Any thoughts would be appreciated!!!

    my knowledge of access is fairly limited, so I appreciate your patience

    Cheers

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'd have a customer table and a people table. The couple (2 people) would both relate to the one customer table entry.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jul 2007
    Posts
    4
    this is probably wrong but hope it makes sense what i am trying to achieve, see attached image, do i enforce referential integrity ???
    Attached Thumbnails Attached Thumbnails customer couple table.JPG  

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I wouldn't do it that way. If you do it the way I suggested, you can record other people besides the bride and groom as well... like who's paying the bill for example.

    However, YES. Enforce referential integrity!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by StarTrekker
    However, YES. Enforce referential integrity!
    What StarTrekker said! (and before you start designing your forms And especially before entering data.)

    Orphaned records are bad.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jul 2007
    Posts
    4
    Thanks for your replies, however I cannot quite visualise what you mean startrekker, are you able to show me what you mean????

    Again excuse my ignorance, sorry!!!!

    cheers
    darren

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Excused!


    CUSTOMER
    ---------
    CustomerID (Primary Key)
    CustomerName
    etc...


    PERSON
    ------
    PersonID (Primary Key)
    PersonTypeID (Foreign Key)
    CustomerID (Foreign Key)
    Firstname
    Lastname
    etc...


    PERSONTYPE
    ------------
    PersonTypeID (Primary Key)
    PersonType (holds things like Bride, Groom, Debtor)

    That kind of thing.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jul 2007
    Posts
    4
    Thanks Startrekker

    That makes perfect sense now!!!!

    I appreciate your time

    Cheers

Posting Permissions

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