Relationship | Table Design

[lanceomni]

I am creating a database for a photography business and I wanted to know If I am going in the wrong direction before I get to deep.

I need to keep track of each job and client along with the contacts that relate to them. I have a table called "jobs" and one called "contacts" In the contacts table I have a "ClientYesNo" field to keep track if this particular contact is a client and I want to have a portal list showing any other contacts related to it.

On my "Jobs" table I have a field to identify the client and also I want to add a portal list showing contacts related to the job.

In my previous files I had "Jobs" "Clients" "Contacts" but figured that the "clients" table was not needed.

Am I going the right way? or have I completely lost you guys.

-Justin

Ocala, Fl

[Fenton]

On my "Jobs" table I have a field to identify the client and also I want to add a portal list showing contacts related to the job.

This statement leads us to believe there is 1 and only 1 Client for a Job, yet there could be multiple Contacts. That would pretty rule out a "clients and contacts" are the same entity structure.

Normally there should be only 1 "people" table. If the only difference between a "client" and a "contact" is that one of them has purchased something from you, then they should be the same table.

But your statement above implies there are not logically the same thing. So you're going to have to explain them better. That is likely why no one has replied to this before.

[lanceomni]

Clients are a business or individual. Each client will have related contacts

Jobs will have one client (usually) These jobs are for architectural photography and so on the jobs layout I list client as the one who commissioned the job. Under a "companies" tab I list out the Builder, Architect, Engineer, Designer and Misc related to the job. I made these fields which are members of a list value called "Clients". One of these mentioned above is equal to the original client.

I have just redesigned a new database to add new fields and so the file I have attached is very rough but should show the general idea of what I want.

In my original design I had a "Jobs" and a "Clients" table. Instead of using a portal to list similar contacts for each job I had fields embedded in the job table for contacts. Home Owner, Home Owner Cell, Forman, Forman Cell and several more. In doing this I found I had been entering redundant information or needed to add information I had no field for. I then noticed the template "Contacts Management" and liked how you could create a portal list of related contacts. I wanted to recreate my database to incorporate this option for each job, each client and maybe even contacts themselves.

I have been working hard at learning fmp by watching Lynda.com tutorial videos. !Great site!

I really appreciate the help here. I hope to one day return favor to others on this board.

Justin

Photography.zip

Edited September 7, 2007 by Guest