Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    7

    Different Client Needs

    Does anyone have any ideas on how to setup a clients database to cater for different client types?

    I am setting up a photography database to cater for wedding, portrait and commercial clients. All three require different info to track.

    For example:
    PHP Code:
    WEDDING CLIENTS
    bride_firstname
    bride_lastname
    groom_firstname
    groom_lastname
    ...
    ...
    ...

    COMMERCIAL CLIENTS
    company_name
    agency_name
    art_buyer_firstname
    art_buyer_lastname
    art_director_firstname
    art_director_lastname
    ...
    ...
    ... 
    I guess the only idea I have is to put all the fields from both of the above tables into one client table and only fill in the fields I need when entering a new client. Not glamourous but I guess it will do the job. The other idea is to setup three different client tables. Any suggestions?

    Thanks.

    Brad
    Last edited by bradles; 12-14-05 at 19:11.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I'd suggest 4 tables (possibly more
    the parent table say TBLCustomer contains the key details of a customer
    eg name, invoiving address, contact details etc. (include any common data from all 3 job types

    you can then link to customer from, say a photo register.

    you then have a child table for each type containing their unique data. you may need to implement more tables if you find data is repeating. (for example you may need to explode the wedding photo further).

    If your db supports exclusive 1:1 joins (ie the :1 can only be one of n tables) then its pretty easy to implement, otherwise its down to implementing that as part of the business logic.

    Personally I'm not sure that this is the 'right' model, but its perfectly tenable. I have doubts about storing attributes which are probably photo (image) related in a customer table. I think you are exposed by storing contact details in the customer table(s), you possibly need to consider a contact type and then store those contacts associatiing them to customer. That way round you can deal with large companies which may have multiple art directors, producers, assistants etc..... (all of whom may have multiple tel / fax / email / web contact data). It also may get you out of the tricky situation of the same sex marriage / partnership of assigning someone to 'groom' and someone else to 'bride'. Your model could then also support the situation say where someone may want photographs of a marrige re-dedication wher ethe labels are 'husband' & 'wife'. If your system generates reports or invoices which could be seen by the customer then it could avoid potentail problems.

    HTH

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    I would create a table for group entities, and then a junction table to place individuals into that entity.

    eg:

    tblPhotoGroup 'This defines an entity. Be it a wedding couple or corporate client.
    -----------
    photo_group_id
    photo_group_category
    address
    etc

    tblPhotoGroupCategory 'This defines types of entities available
    -----------
    photo_group_category_id
    description

    tblClients 'This defines individuals that make up an entity
    -----------
    client_id
    fname
    lname
    address
    etc

    tblPhotoGroupClients 'this groups clients together into a single entity (wedding partners for example)
    -----------
    photo_group_id
    photo_group_client_category_id
    client_id

    tblPhotoGroupClientCategory 'This defines the role a client has in the PhotoGroup (bride/groom/corporate exec)
    -----------
    photo_group_client_category_id
    description
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2005
    Posts
    7
    Thanks guys.

    Teddy...I'm not sure I understand the relationships of your example clearly. Could you show specifically with fields how these tables would relate?

    Brad.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Just create all the tables mentioned. If you see the two fields with the same name, draw a line between them.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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