Solved

SQL Database Design - best practice

Posted on 2013-11-21
6
513 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
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 26

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 48

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
two ways encryption with php 3 28
CPU high usage when update statistics 2 30
SQL View nearest date 5 36
MS SQL Server COnditional Where statement 7 56
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

773 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