Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Incorporate additional requirements into a legacy database design

    I am struggling with a database design, this is what I have so far.

    Click image for larger version. 

Name:	schema.png 
Views:	1 
Size:	100.3 KB 
ID:	14966

    Here are the problems.

    1. I need to introduce a new user type (conglomeration manager), and they will have visibility of groups of companies (a conglomeration). A conglomeration manager can have multiple companies, and a company can belong to multiple conglomeration managers. It would be advantageous if an independent company could be added, and then at a later date be easily included as part of a conglomeration.

      I am finding this difficult to model as all my users so far (manager,driver,recipient) all exist in the users table. This was by design as they all have nearly the same data fields, and I need to have a single login point for all users on my site. If I add a conglomeration manager to the users table, they will have relationships with other tables that my existing user types don't have.

    2. I am uneasy about the dependency loop formed through users, ownerships, packages, companies, users. This strikes me as bad form, but I really can't think of a way to avoid it as:

      managers, drivers and recipients all work for a single company. That company has an associated set of packages, yet I need to have the ability to associate a subset of those packages to a particular recipient (they own packages) and to a particular driver or manager (responsible for delivering those packages).

    3. I am not happy with the "receive_emails" field in users, as it is only relevant to users of type "recipient".


    To add to the the problems, this design is already in use, and data will have to be migrated to any new design.

    The most common operations that take place in the system are the viewing of statuses by recipients, followed by the creation of statuses by managers and drivers.

    Can my problems be addressed with an elegant new design?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'd consider using a linking table to connect your existing tables. As an example, create a table to link users to companies which will allow you to have both employees and the conglomerate managers.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2013
    Posts
    2

    Thanks for reply

    Thanks for the reply Pat, but this introduces a problem as other users, driver, recipient, manager have no concept of a conglomeration.

    Is there any way to get rid of the loop I mention?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm pretty sure that my suggestion avoids it.

    -Patp
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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