Relationship Graph

lanceomni

Lately ive gotten into PHP and would like to start from scratch designing a database and creating a website to serve this information. I have previous exsperience with filemaker so I understand to some extent db.

I want to cover all the bases with my db design and not paint myself into a corner.

The database will keep track of an architectural photography business. Each JOB will have a CLIENT (builder, architect....) who requests the shoot. Other CLIENTS related to the job may also need to be referenced.
A CLIENT may be an individual "CONTACT" or a "BUSINESS" (Not sure if I should combine these because I keep heavily detailed information on both types ie 50+ fields in some cases and separating them would simplify it for me.). Additionally a "BUSINESS" may have several "CONTACTS" associated with it.
I would also like to keep track of associated "CONTACTS" and possibly "BUSINESSES" related to a specific job "JOBS"

It looks to be difficult in that every time I draw a relationship graph I get one or more many-to-many relationships. I have enclosed a few drawings to show my ideas. Using Filemaker I have been pretty happy with my results. I have painted myself into a few corners but they are acceptable for what I am using it for. Id like to avoid this upfront

Attached is an example of my relationship graph.

Am I on the right track? Any ideas?

Thank You

rulian

It's all more relevant to the types of information you will be using.
You are on the right track, you simply need to analyze the data coming in and see what can be shared or can avoid repeating.
If your businesses and contacts share the same information to different levels then you can pool those together. If each contact can have multiple fields associated with it it's best to build a table which holds many rows per contact versus many columns on a table. Businesses and Contact are simply different types of accounts you can list an accounts table with a "type" column for each entry. That table can then serve as a key for your contacts table where you can enter contact information in rows for each account. You can then have a job table detailing the job info, a job request table to allow for multiple accounts to be associated with a job.

A basic schematic, provide some sample data and we can be more helpful

lanceomni

Thanks,

If I want to associate a "client" with a "job" and a "client" can be a "business" or a "contact" would I do the below?

                         business

job ----- clients <
contacts

tbl_Clients
col_client_id
col_business_id
col_contact_id

Would I populate the clients table with a contact or biz id depending on what kind of contact they are?

If I had a column in both tbl_business and tbl_contact called col_client
then populated it with Yes or No how would I then be able to reference it to a job.

I will draw better schematic and provide some data as an example. Ive also bought MtSQL second edition by Larry Ullman so I am really trying to get this stuff.

Thank You,
Justin

NogDog

The client table seems superfluous to me, unless there is some kind of unique data to be stored there. Assuming there isn't, the jobBUSINESSES and jobCONTACTS tables seem to give you all the relationships you need.

A second thought I had is to define a default contact as a sort of place-holder for each business, and then only associate jobs to contacts. Then when processing a list of contacts for a given job, you could just throw in a little special logic for when that contact is identified as a "default" rather than actual person. This would simplify things to something like the attached.