Solved

SQL Database Design - best practice

Posted on 2013-11-21
6
502 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now