ER Modeling; multiplicity basics - one to many or many to many?

Question

Apologies for a basic question, but I’m having difficulties determining the multiplicity constraints for a database I’m trying to design.

Our team undertakes a number of services/projects...each involve (at least one) activity (prototyping, evaluations, design). The database is intended to record details of each project we complete, and which activities were used in a particular project (plus other details).

Now, down to my confusion:

In one sense, it is a one to many relationship (1..1 1..*); each project involves at least one and possibly multiple activities.

But, if considering it from a different perspective, it is a many to many relationship (0..* 1..*); an activity could be in the database that is never used in any project on record, and, the same activity could be undertaken in multiple projects (for example, multiple projects could involve ‘design’).

Which perspective is correct?

Kind regards, Mitch

Answer 1

I think your design could be stated more formally as:

One company has many projects. 
One project belongs to one company.
One project has one or more activities.
One activity has exactly one activity type (prototyping, design).
One activity belongs to exactly one project. 

So, if that's true, you have the following tables:

COMPANY
CompanyID
....

PROJECT
ProjectID
....

ACTIVITY_TYPE
ActivityTypeID
Name (e.g. "Prototyping", "Design")

ACTIVITY
ActivityID
ProjectID
ActivityTypeID
StartDate
EndDate
...

Answer 2

Your question is one of analysis, and not design. You can design a database for either answer to your question.

What's the cost of getting it wrong? If you design it for many to many, and it's really one to many, you end up with an extra table and slightly slower joins. If you design it for one to many and it's really many to many, you won't be able to store all the mecessary facts in your database.

In view of the above, which is the better bet?

Answer 3

In a standard relational database, a many to many relationship will always be represented as a pair of one to many relationships - in your example, using a ProjectActivity table

So you have

Projects

ProjectID | ProjectName | ...

Activities

ActivityID | ActivityName | ...

ProjectActivity

ProjectID | ActivityID | ...