Hello,

Myself and my colleague are attempting to design a database model and are having some disagreements along the way. We have come up with a model that we somewhat agree upon but neither are entirely happy with!

Attached is a picture of the DB model we have come up with so far. The requirements of the DB are as follows, it's for a campaign (project) management application.

We have companies, which have multiple users (employees) belonging to them. Companies also have company_groups (divisions) that employees belong to. One employee can belong to one or more company_groups.

We also have campaigns (projects), a campaign can belong to one or more companies. Each company needs to have different access permissions to a campaign.

Each campaign has 1 or more campaign_groups (these are used to specify user access level to a campaign). Each campaign_group can have many company_groups belonging to it and each company_group can belong to many campaign_groups.

While the picture is created in MS Access, this was only used for modelling, and it will actually be built in MySQL.

Does the model we have created look correct to you DB gurus please?

Thanks in advance for any help

Scully

Do you have a URL or a PDF for the assignment as you got it from class? What kind of class is this (database, programming, or other) because that will influence the solution you want to turn in too.

-PatP

Hi Pat,

It's not for a class at all, we're creating a web based application and this is going to be one of the backend DBs for it. Obviously we would like to conform to all best practices etc. but there won't be anyone examing our work!

Please do let me know if I can provide any more information that would be helpful.

Thanks, Scully

in your many-to-many relationship tables, remove the useless ID, and make the PK a composite key consisting of the two FKs

Thanks r937,

I've revised the model based upon your suggestion.

Does this look alright now?

EDIT: I've also uploaded the ER Diagram

Thanks, Scully

Do users have to belong to a group, or can they just belong to a company? Do you need to track subsidiaries (where many companies can be owned by a single holding company, or something like that)?

-PatP

Hi Pat,

Users have to belong to a company.

As to users having to belong to groups, it could be either, and I think we could be flexible on this depending upon which would be best / easiest to implement. Users will have to be members of groups to have any access to campaigns. However, we could either choose to have users who are not yet assigned to any group or we could force users to be members of at least one group on their creation (possibly to a default unassigned group if necessary). My personal preference is that users shouldn't have to belong to a group as this could create 'lost' users if a a particularly user belonged to a group and that group was then deleted. We can force reassignment of that user to another group, or to a default unassigned group but it just seems a bit messy to me. My colleague disagrees with me on this one though, so we will go with the consensus on here!

Users must be able to be members of multiple groups though.

There's no need to track holding company and subsidiary relationships, although the system has to allow for companies to work in partnership on a single campaign. So one company can "own" the campaign, but another can be granted access to work on that campaign.

Thanks, Scully

At a first pass it looks like your ERD is sound.