December 7, 2011 at 10:21 pm
Hello,
I am working on designing the database from scratch.
To get the optimized performance and Simplify Maintainability i have thought to split the database (say ERP) into 3 parts (say, ERP-Module1, ERP-Module2, ERP-Module3). And in future if requirement grows new module can be added to the database (say ERP-Module 4).
My question is, what i have mentioned is correct approach?
What are other ways to build the optimized database?
NB: ORM (Entity Framework) will be used for CRUD functionality.
Thanks !!!
December 8, 2011 at 12:01 am
Few Questions:
•What do you mean by split? 1 database split into multiple databases or multiple schemas?
•My understanding on ERP modules is they need strict isolation in most of the cases. E.g. HR details shouldn’t visible to Marketing dept. On the same time some selective information should be visible cross modules. Still I believe splitting is a good idea.
•Now it comes to ownership of application. Are there individual owners for modules or there is one owner for ERP application?
I am not sure how splitting would work with ORM. It requires a research on your end (& I will happily help you in that because it’s interesting topic ;-)). Please feel free to PM me.
December 8, 2011 at 12:52 am
Hello thanks for the response.
The scenario is as follow:
ERP is web based application so there will be many different organization going to use it.
1. Yes: ERP modules need strict isolation as you wrote. Keeping those point in mind is it possible to split ERP database to get the performance and optimization.
2. About the ownership of application, it will be based on roles, i guess that can be handled by the Asp.NET (roles and membership)
December 8, 2011 at 1:00 am
ERP is web based application so there will be many different organization going to use it.
Are you hosting something for your customers? Would you please describe it?
December 8, 2011 at 1:10 am
What i know it will be the subscription based ERP (initially following modules,Customer,Membership,Roles,Permission, HR, Finance in its beta edition and later on other modules will be created)
Client will register for the application to use, and they will create different Roles and Permission within the application.
Client will maintain their own data.
December 8, 2011 at 6:16 am
gopalchettri (12/8/2011)
What i know it will be the subscription based ERP (initially following modules,Customer,Membership,Roles,Permission, HR, Finance in its beta edition and later on other modules will be created)Client will register for the application to use, and they will create different Roles and Permission within the application.
Client will maintain their own data.
When you are hosting their data, how can they maintain it? Are you / your organization is planning to give control of database to individual clients? It would be royal PITA.
It’s more complex than you are imagining now and you seriously need a Professional to design the database for ERP application for you. I can help you but it would be a paid service.
December 8, 2011 at 2:37 pm
gopalchettri (12/8/2011)
Hello thanks for the response.The scenario is as follow:
ERP is web based application so there will be many different organization going to use it.
1. Yes: ERP modules need strict isolation as you wrote. Keeping those point in mind is it possible to split ERP database to get the performance and optimization.
2. About the ownership of application, it will be based on roles, i guess that can be handled by the Asp.NET (roles and membership)
Let me check my understanding of the scenario.
Your company plans to sell ERP services to different customers, is that correct?
In the affirmative case - for security as well as backup/recovery issues - you may want to host each one of your ERP customers in a different database.
In regards to splitting modules I'm not sure what you mean. Is that a logical splitting that goes no beyond of naming convention? Is that a logical splitting that assigns a particular schema-owner to each module? I'm afraid to ask this one but, is it a physical splitting where you are planning to host different modules in different databases?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 10, 2011 at 3:53 pm
Let's keep it public meaning, no private messages unless you are planning to hire me 😀
So... as per your message the idea is to keep the data of all your customers in the same database for "maintenability and performance concerns".
Well.. please think about the next items...
1- One of your customers wants you to restore "his database" as it was last monday at 2:46PM... how are you going to do that if all your customers are in the same database? Remember... you backup and restore whole databases (I know you can do filegroup but I do not think you are planning to have a filegroup per customer).
2- One of your customers goes bankrupt and is not longer a customer... how are you going to get rid of the data of that particular customer? by deleting rows in a zillion tables?
3- I have a saying... even crappy code - please read suboptimal code - appears to perform well in a small database so... if you are planning to store the data of all your customers in a single one, chances are your code is going to start showing its flaws faster than expected.
IMHO ... my first choice would be to have a database per customer, second option (which I do not like that much) is to have a schema per customer.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 10, 2011 at 9:52 pm
Thanks for the suggestion. !!!
But sorry i can't hire you 🙂
Thanks again, you put me in right direction !!!
December 11, 2011 at 7:10 am
The last replies have been to have a db per customer in your set up. That's the one I would go for for simplicity of management, upgrades, specific client tailor work, ...
You simply cannot build a system that suits all your clients. They will come up with their "special" needs.
That's where the great profit comes with all SAP systems. ;-):hehe::w00t:
Regarding your original modular approach, that's one thing people use schema for.
Keep in mind _not_ to use the dbo schema, but create your own to host your objects.
You could also use filegroups on a per module basis, ( with or without separate schema ) to support and maintain your objects, but even there, you need to keep in mind not to over do it.
Design all objects ( not only tables ) to the optimal, put relationships in place and have them enforced.
As you are handling OLTP, do your data analysis at least to 3NF.
That is not a wast of time because it will pay off during maintenance later, it will help you and your team to better understand the data and not abuse columns for purposes they weren't intended for.
It will also help to nicely distinguish the schema your objects will reside in, hence align your different domains of data.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply