What is best database design architecture in SQL server for multiple ERP solutions ?

Question

1.00/5 (1 vote)

See more:

, +

C#

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 .

Posted 15-Oct-16 12:03pm

Member 10377426

Updated 15-Oct-16 20:58pm

SoMad

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdi Gholam · Accepted Answer · 2016-10-15T20:33:00

Solution 1

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.

Posted 15-Oct-16 20:33pm

Mehdi Gholam

Comments

[no name] 16-Oct-16 12:10pm

so what is best approach ^^ Multiple Years Databases For Every Solution or Multiple Years for All ERP solutions ?

Mehdi Gholam 16-Oct-16 13:15pm

Start with a single database for all apps and all years.

[no name] 16-Oct-16 22:15pm

What i mean we have an executable project that shows All Years Databases, The Client Choose The Year where he need to works with, because every year has custom transactions, sales. but if i make single database that contains all years. this maybe hard to maintenance if i need to retrieve Year2007 tables or rows... and also the single database if i need backup from client i will take more than 2 GB file ! can you tell me what good approach if i need to works with Multiple Years database, and i just can gain current year backup from client easy and small size...? Thanks ,

Wendelius · Accepted Answer · 2016-10-15T20:58:00

Solution 2

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[^].

Posted 15-Oct-16 20:58pm

Wendelius

Comments

[no name] 16-Oct-16 12:14pm

Yes but our users must Clear The Previous Database Year ... I Mean DeAttach Previous year with Project Executable ?
what i understand for you that only one database for all years is good. and show only custom year inside Project Executable ? is that what you mean. anyway good answer but i want good tehcnique can you gimme some tips .... Thanks !

Wendelius 16-Oct-16 13:11pm

Not sure what you mean by clearing the previous database year, but if you want to retrieve values only from current year, then that would be a condition in the SQL statements. For example if you have a RowAdded column in a table then you would have a comparison that RowAdded is greater than or equel to first day of the year.

But again having data for current year only seems a bit odd requirement for an ERP system.

[no name] 16-Oct-16 22:15pm

What i mean we have an executable project that shows All Years Databases, The Client Choose The Year where he need to works with, because every year has custom transactions, sales. but if i make single database that contains all years. this maybe hard to maintenance if i need to retrieve Year2007 tables or rows... and also the single database if i need backup from client i will take more than 2 GB file ! can you tell me what good approach if i need to works with Multiple Years database, and i just can gain current year backup from client easy and small size...? Thanks ,

Wendelius 17-Oct-16 10:14am

I'm not sure if I follow, but as I said previously, personally I would use a single database for all years and a single table also. So I wouldn't separate the data based on the year.

I'm not sure why you're worried about a 2GB backup size. With current disk sizes, 2GB is hardly nothing, far less than a DVD.

My point is to keep everything as simple as possible from programming point of view and let the database do it's job, handle the data properly.

But as also said there is no single best solution because of different affecting factors so you have to gather information, consider different options, and make the decision based on all requirements you may have.