Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com Sep 12 '12 at 23:26

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

1  
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
add comment

3 Answers

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:

COMPANY
CompanyID
....

PROJECT
ProjectID
....

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

ACTIVITY
ActivityID
ProjectID
ActivityTypeID
StartDate
EndDate
...
share|improve this answer
add comment

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
add comment

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 | ...
share|improve this answer
add comment

Your Answer

 
discard

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