Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Database Design - best practice

Posted on 2013-11-21
6
Medium Priority
?
567 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 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 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