Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2008
    Posts
    11

    Flexible Database Design for Multiple Clients

    I am designing an application for Company "A" which performs contract maintenance work at power plants throughout the US. One of the primary functions of the application will be used to track costs for Company "A" as well as for their clients/power plants.

    The clients' accounting systems are diverse (e.g. some use SAP, JDE, etc) as well the number and type of accounting data that each wants to "capture". For example, one client may want costs reported by SAP#, Work Authorization#, and Purchase Order#. Another client may want costs reported by Work Order# and Release#. There are usually 2 - 4 "pieces" of accounting data needed.

    I would like to create a table structure that will be flexible enough to accommodate each client. In the first example, there is a 1-many relationship between SAP# and Work Authorization#, and a 1-many relationship between Work Authorization# and Purchase Order#. In the second example, there is a 1-many relationship between Work Order# and Release#.

    One option that I have considered is creating one ("un-normalized") table with a PK consisting of 4 fields ("Level1", "Level2", "Level3","Level4").

    Another option is to create 4 tables ("tblLevel1", "tblLevel2", "tblLevel3","tblLevel4") with 1-many relationships between each.

    Are there any other options? Any suggestions? The database will most likely be SQL Server.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Option 1: Ugh.
    Option 2: Ugh.
    Other option: create your schema to support many-to-many relationships, which will of course also support one-to-many relationships.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2008
    Posts
    11
    Can you give an example? I'm assuming this involves intersection tables?

  4. #4
    Join Date
    Oct 2008
    Posts
    11
    Can you give an example? Thanks.

  5. #5
    Join Date
    Oct 2008
    Posts
    11
    oops! sorry for the second reply!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by BARJRD
    In the first example, there is a 1-many relationship between SAP# and Work Authorization#, and a 1-many relationship between Work Authorization# and Purchase Order#. In the second example, there is a 1-many relationship between Work Order# and Release#.
    Explain to me how these requirements are exclusionary.
    You mention five different tables for the two scenarios, and neither scenario uses tables referenced in the other scenario. I don't see how these overlap, or what would prevent you from implementing both sets of relationships.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2008
    Posts
    11
    Here's a better example:
    Client A has the following cost accounting structure:

    1) SAP#
    2) Work Authorization#
    3) Purchase Order#

    For each SAP#, there are many Work Authorization#s, for each Work Authorization# there are many Purchase Order#s.

    Client B has the following cost accounting structure:

    1) Work Authorization#
    2) SAP#
    3) Work Order#
    4) Activity#

    For each Work Authorization#s, there are many SAP#s, for each SAP# there are many Work Order#s, for each Work Order# there are many Activity#s.

    Client C has the following cost accounting structure:

    1) JDE Job#
    2) Cost Acct#

    For each Work JDE Job#, there are many Cost Acct#s

    So... the data that must be captured for each client is diverse as well as the number of "levels" of data.

    Does this make sense?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I'm sorry to tell you this, but if you have two companies with such drastically different accounting practices, (likely neither of which conform to GAAP), then I think you idea of creating a single application to service both of them is a pipe dream.
    Either companies conform to best-practices, in which case they get software that is designed to the highest standards, or they insist upon their own idiosyncratic accounting methods, in which case they need to pay for custom software.
    Experience tells me that nothing you do to try to consolidate these two accounting systems is going to be practically feasible.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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