Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    4

    Smile Having a DB Model Headache!

    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
    Attached Thumbnails Attached Thumbnails DB_Model.gif  

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Jun 2007
    Posts
    4
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in your many-to-many relationship tables, remove the useless ID, and make the PK a composite key consisting of the two FKs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Posts
    4
    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
    Attached Thumbnails Attached Thumbnails DB_Model_revised.gif   ERD revised.gif  
    Last edited by ScullyDB; 06-21-07 at 12:42.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  7. #7
    Join Date
    Jun 2007
    Posts
    4
    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
    Last edited by ScullyDB; 06-22-07 at 05:59.

  8. #8
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    At a first pass it looks like your ERD is sound.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •