Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Sep 12 '12 at 23:26

This question came from our site for professional and enthusiast programmers.

both, if you have a defined list of activities, then its many to many, if activities are custom for each project (like free text) then its one to many – Hawili Sep 12 '12 at 11:07
up vote 2 down vote accepted

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:



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

share|improve this answer

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?

share|improve this answer

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


ProjectID | ProjectName | ...


ActivityID | ActivityName | ...


ProjectID | ActivityID | ...
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.