First time posting here.
I've been trying to design entity relationships for a database but I am getting a little confused on how to go about relating a few tables.


A project is done for one owner company, an owner company can have multiple projects.
A project can have many consultant companies, A consultant company can do many projects. But a project can have only one prime consultant company.

Consultant Companies can be represented by multiple contacts (individuals). A contact can only represent one company
A Project can have multiple Consultant contacts But there can be only one prime Consultant contact for each Project.

Owner Companies can be represented by multiple contacts (individuals). A contact can only represent one company
A Project can have multiple Owner contacts But there can be only one prime Owner contact for each Project.


With
+====< being (1:m)
and
>====< being (m:m)

These are the Relationships I’ve drawn out:

Owner company +=====< Project >=====< Consultant company

Owner company +=====< Owner Contacts

Consultant company +=====< Consultant Contacts

Owner contact >=====< Project >=====< Consultant contact

My concern is that the relationships haven’t been drawn properly and that the Prime contacts wont be detected. Could you please verify if I am on the right path or even propose a better way of doing it. Thanks.

yes you are on the right path

first thing to consider: a relationship needs a name

you're missing the relationship from project to prime consultant contact

you already have one (the m:m one between project and consultant contact) but you need another one (a 1:m one)

hence they need to be named in order to distinguish them

second thing to consider: contacts are people, and you might want to haev a supertype/subtype structure for people and the different types of contacts that they can be

if an owner contact can also be a consulting contact on a separate project, your current scenario would have you entering the same person in two places

Since the project can only have one Prime consultant company and A consultant company (primary or regular) can only have one Prime consultant contact do I still have to relate the project directly to a Prime consultant contact?

If you are mean hiararchies by supertype/subtype then I'd say the contact information doesnt go into that much detail. Just the basics and then if prime contact or not.

I initially wanted to have one contacts table that would differentiate if it was a Owner or Consutant contact. Will this be problematic considering Project and Consultant Company has a M:M relationship where as Project and Owner Company have a 1:M relationship?


Thank you for the quick reply.

you do not, provided that the same person is the prime contact on all projects

which i see as unlikely

Thanks Again :-)

So I went and talked with the stakeholders to clarfiy and obviously you were right.

But I still remain a little confused.
I have updated and reorganized the relationships.
With
+====< being (1:m)
and
>====< being (m:m)

Owner Relations
A project is done for one Owner Company; an owner company can have multiple projects.
Owner company +=====< Project

Owner Companies can be represented by multiple contacts (individuals). A contact can only represent one company.
Owner company +=====< Owner Contacts

A Project can have multiple Owner contacts and an individual owner contact can be related to multiple projects.
Owner contact >=====< Project

I am stuck HERE
A Project can have only one Owner contact as the Prime Owner contact. An owner contact can be the prime owner contact for multiple projects.
How do I relate the owner contact, the project and the prime owner contact?

Consultant Relations
A project can have many consultant companies, A consultant company can do many projects.
Project >=====< Consultant company

Consultant Companies can be represented by multiple contacts (individuals). A contact can only represent one company
Consultant company +=====< Consultant Contacts

I am stuck HERE
A Project can have only one Consultant Company as the Prime Consultant Company. A Consultant Company can be the Prime Consultant Company for multiple projects.
How do I relate the Consultant Company, the project and the Prime Consultant Company?

I am stuck HERE as well
A Project can have only one client contact as the Prime client contact. A client contact can be the prime client contact for multiple projects.
How do I relate the client contact, the project and the prime client contact?

The relationships will typically be the exact same I believe.

data modelling is really simple if you have sample data

getting sample data is tedious but simple

do you have any sample data for this project?

because if it's a homework assignment, you won't, but if it's a real-world project, you will

Its a real world project .... i m doing it for large but cheap construction firm as a contractor.
Anyway I'll put data in tables asap.

The data in the table are generic and can be added... but I created the entities for each of the tables that I thought were necessary. I just want to
know if these tables are enough and can be used to derive prime owner contact, Prime consultant and Prime consultant contact.
Notice the Owner is referred to as Client in the tables.
let me know if this helps
thanks ahead of time.

Hoping to get some help with this still.

sure, what help would you like?

I am still where I left off on Friday It was a nice weekend full of rain . Hope yours was good as well.

Also Wanted to know what is the best techniques you use to figure out which tables and relationships need to exist to meet certain rules.