Solved

SQL Database Design - best practice

Posted on 2013-11-21
6
550 Views
Last Modified: 2013-11-26
Experts,

We have a MS SQL database that we are planning to re-develop. We have 2 databases with stored procedure that communicate with each other via our in-house application we have developed.

We have several products tied into 2 databases. Our aim is to re-develop the database and compile the database into one database and have each of our products in a separate database.

We want to know from you experts, what is the best practice on how we can improve our database, or is there an example of a database structure we can follow.  


Regards,
0
Comment
Question by:Sevron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39666791
Here is Microsofts best practice Guide lines, hope it helps

http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39667102
"Our aim is to re-develop the database and compile the database into one database and have each of our products in a separate database."

This sounds like a contradiction. Do you need 1 or 2 databases? Also this is a way to general description and is practically not possible for someone to give a useful answer. More details maybe of how the product structures are and maybe how they are used.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39667977
are you describing this perhaps?
as-Is
  -------------------
  db-1  <procs>  db-2
  -------------------
  ^     ^     ^     ^
  |     |     |     |
Prd-a Prd-b Prd-c  Prd-?


To-Be
  -------------------
      db-central     <<< i.e. the changes are here (mostly)
  -------------------
  ^     ^     ^     ^
  |     |     |     |
Prd-a Prd-b Prd-c  Prd-?

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Sevron
ID: 39668348
Hi the re-developed database will be 1 database which several products all use.  Initially a single product will be linked to the new database with the other products still using the old databases. Over time we plan to re-develop our products so they can be moved over to the new database.

Our General idea is to break the data we hold down into many smaller tables creating relationships between them via primary keys and foreign keys. The alternative to this approach would be to have a small number of very large tables.

Some guidance on which approach is better and a brief over view of other best practices when under taking a large database re-development would be great.
0
 
LVL 9

Accepted Solution

by:
QuinnDex earned 500 total points
ID: 39669915
I would go for the many smaller tables over few very large tables every time, will cut your over heads considerably.
0
 

Author Closing Comment

by:Sevron
ID: 39677268
Thank you for your help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question