Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dbar
Starting Member
12 Posts |
 Posted - 2008-10-07 : 11:48:00
|
| I am designing an application for Company "A" who 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 the client/power plant. 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 "captured". 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? |
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-11 : 11:40:48
|
| It might be more efficient to have single table if the number of properties (different attributes for different clients are not too many, like in 100s).read and write from the table would be faster. |
 |
|
|
|
|
|
|
|
Flexible Database Design for Multiple Clients