?
Solved

SQL Database Design - best practice

Posted on 2013-11-21
6
Medium Priority
?
572 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 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 50

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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

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 2000 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

589 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