什么是SQL服务器中用于多个ERP解决方案的最佳数据库设计架构？ [英] What is best database design architecture in SQL server for multiple ERP solutions ?

问题描述

问题： 最佳数据库设计架构在 SQL Server中对于多ERP解决方案？ 
我已经看过微软的AdventureWorks数据库架构，它看起来很酷但很多继承而且我不适合它。 
 
我的意思是我想设计一个ERP解决方案加汽车租赁系统，医院系统和酒店系统等任何商业解决方案。所以我决定在SQL Server中创建一个数据库模式，为示例：
 
我猜想的模式示例
 
 dbo.schema   默认表 as  ErrorHandling，BuildVersion .. 
 
 HR Schema  人力资源
库存模式 for 库存和销售系统
会计模式 会计系统。 
等等... 
因此表格看起来像这样：
 
其中的每个表格' < span class =code-string> s schema system  -  Inventory.Sales 
 
 Inventory.Purchases 
 Inventory.Reports 
 Inventory.OrderHeader 
 Inventory.OrderDetail 
 
 Car.Contracts 
 Car.OpenedContracts 
 Car.ClosedContracts 
 Car.Types 
 Car.etc ..>> 
 
 Accounting.Transcations 
 Accounting.CostCenters 
 Accounting.AccountL1 
 Accounting.AccountL2 
等等.. 
 1-But ..我也想让我的数据库像这样的结构：
 
  - 年份数据库，每年年初重置它并开始新的数据库。 
 ERPYear012016 
 ERPYear012017 
 ERPYear012018 
 
  - 包含静态项目的一般数据库（车型，人员）
 ERPGeneral01  - >例如，公司编号为
 ERPGeneral02  - >公司编号2 
 
但我发现这个想法会使我的数据库太大，因为每个系统将超过100 +表？这会使查询中的成本性能变慢并影响数据库本身吗？ 
 
 2  - 或者我应该让每个系统独立吗？ 
 
例如：
 
 CarYear012016 
 CarYear012015 
 
 InventoryYear012016 
 InventoryYear012017 
 
这样做性能好而不是只有一个数据库包含一切？ 
因为并非所有客户/客户都会购买所有套餐，一些客户只需要购买汽车系统套件......这就是为什么我需要了解制作卓越性能和良好设计的最佳方法？ 
 
我需要好的设计
高性能
快速查询
我还想把这个注意：< span class =code-keyword>如果我为每个系统制作独立数据库，如Car System：
 
 CarYear012016 
 CarYear012017 
 
i这里可以插入许多模式 我可以用于汽车系统，这使数据库很好理解 示例。 
合约架构
汽车类型架构
租赁架构
   此好> ？我不知道' 。 
 
主要问题是哪种方法在设计和性能方面表现良好？ 
 
第一个：在年度数据库中插入所有ERP系统（2015,2016，...）？ 
 
第二种方法：在每个数据库中插入每个ERP（Car2016，Car2017，Inventory2016）？ 

我尝试了什么：



我尝试过AdventureWorks方法，但似乎不能很好地适应ERP解决方案，因为我想要添加年份数据库。请告诉我好方法。

解决方案

您可以随时为每年创建新数据库，这取决于您的用户是否需要查询一年内的数据时间段与否（从不同数据库查询多年的结果可能会有问题）。



将数据分解成数年（进入不同的数据库）会更有效率因为数据会更少。



将数据分解为app数据库​​将取决于您需要跨多少app表查询，这可能会使您的查询更多也很困难。

有很多影响因素。一些想法：

• 在不同的数据库中使用完全不同的模式是有意义的。特别是如果您考虑仅许可部分产品，模块化方法将使许可更容易。例如，您只能购买帐户模块等。但是，如果您的系统将要进行大量的跨职能查询，那么拥有单个数据库的性能会更好，或者至少更容易使性能更好。
• 在我看来，表的数量并不是重要的表现因素。你如何使用它们。我认为，为了保持pf表的数量更小，对标准化成本做出不必要的妥协是没有意义的。这很容易导致非规范化，然后再次导致重复数据再次导致额外操作使其保持最新状态或者您有错误数据。
• 现在要将数据库打破多年，我不知道不这样做。这使结构复杂化并导致维护操作。如果数据库设计和维护得当，您可以毫无问题地处理大量数据。如果数据量真正开始导致问题，那么您将拥有程序不可见的技术可能性，例如分区表和索引 [ ^ ]。

Question : What is best Database Design Architecture in SQL Server For Multiple ERP Solutions ?
I Already looked at AdventureWorks Database Schema of Microsoft, it looks cool but many inheritance and i didnt fit with it.

What i mean by question that i want to design an ERP Solutions plus Car Rent System, Hospital System and Hotel System and so on of any Commercial Solutions. so i decide to make a database Schemas inside SQL Server, for example :

Examples Of Schema that i guessed

dbo.schema for default tables such as ErrorHandling, BuildVersion ..

HR Schema for human resources
Inventory Schema for inventory and sales systems
Accounting Schema for Accounting system.
and so on ...
so the tables will look like that :

every tables inside it's schema system - Inventory.Sales

Inventory.Purchases
Inventory.Reports
Inventory.OrderHeader
Inventory.OrderDetail

Car.Contracts
Car.OpenedContracts
Car.ClosedContracts
Car.Types
Car.etc..>>

Accounting.Transcations
Accounting.CostCenters
Accounting.AccountL1
Accounting.AccountL2
and so on ..
1-But.. i also wanna make my databases like this structure:

-- Year Databases to reset it every beginning of year and start new one.
ERPYear012016
ERPYear012017
ERPYear012018

-- General Database that contains Static Items ( Car types, Persons )
ERPGeneral01 -> For example Company number one
ERPGeneral02 -> company number 2

But I found this ideas will make my database too large because each system will be more than 100 + Tables ? does this make cost performance to be slow in Query and affect the database itself ?

2-Or should i Make Every System Standalone ?

for example :

CarYear012016
CarYear012015

InventoryYear012016
InventoryYear012017

does this make performance great rather than only one database contains everything ?
Because not all our Customers/clients will purchase all packages, some customers need only Car System Package... that's why i need to know the best way to make Great Performance and good design ?

I Need Good Deisgn
High Performance
Fast Query
Also i wanna put this notes: if i make standalone database for each system like Car System :

CarYear012016
CarYear012017

i can here insert many schemas as i can for car system, and that make database good to understand for example .
Contracts Schema
Car Types Schema
Rent Schema
is this good >? i don't know.

The Main Questions Is Which Approach Is good in design and Performance ?

First One : Insert All ERP Systems inside Years Databases ( 2015,2016,..) ?

Second Approach : insert Every ERP in single Database ( Car2016,Car2017,Inventory2016) ?

What I have tried:

I tried AdventureWorks Approach but it seems can't clearly fit with ERP solution because i want Add Years Databases. SO PLEASE TELL ME GOOD APPROACH .

解决方案

You can always create new databases for each year whenever you want, it depends if your users require to query for data over a year time period or not (querying multiple years from different databases into one result can be problematic).

Breaking up your data into years (into different databases) will be more efficient since there will be less data.

Breaking up your data into "app" databases will depend on how much you require cross app table queries, which might make your querying more difficult also.

There are a lot of affecting factors. Some thoughts:

• Having the completely different schemas in different databases would make sense. Especially if you consider licensing only parts of the product, a modular approach would make licensing easier. For example you can buy only accounts module etc. However if your system is going to have a lot of cross-functional queries, then having a single database would perform better or at least would be more easy to make perform better.
• In my opinion the amount of tables isn't significant performance factor. How you use them is. I believe that it doesn't make sense to make unnecessary compromises on the cost of normalization just to try to keep the amount pf tables smaller. This easily results in denormalization which then again causes duplicate data which again causes either extra operations to keep it up-to-date or you have false data.
• Now what comes to breaking the databases to years, I wouldn't do it. This complicates the structure and causes maintenance operations. If the database is well designed and maintained you probably can handle very large amounts of data without problems. And if the amount of data really starts to cause problems you have technical possibilities which are invisible to the program, for example Partitioned Tables and Indexes[^].

这篇关于什么是SQL服务器中用于多个ERP解决方案的最佳数据库设计架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！