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: 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

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

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

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.

Just create all the tables mentioned. If you see the two fields with the same name, draw a line between them.