Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Poboy1975
Starting Member
1 Post |
 Posted - 2009-05-01 : 12:17:56
|
| Our small software company is slowly crawing out of the dark ages moving from COBOL into the .Net, SQL world. Having never designed databases before from the ground up, I am forced to migrate from COBOL Vision flat files into a RDBMS with little assistance and a certain newbie attitude. As you may expect, the pressure is on to get it right from the start.We design software for MIS Distribution purposes. Typical Product, Customer, Vendor, Orders, Purchase Orders, etc. Our customers are companies that have several locations that need each location's data seperate (each location may have their own inventory, customer base, etc.) In the past, each location had a seperate physical file with the LOCATION ID appended to the front (XXAPMA was the Accounts Payable Master File to hold Vendors for Location XX) Since all of our tables in SQL Server 2005 need the notion of a LOCATION ID, I was just wondering how best to design each table in regards to the primary key and indexes. Please assume there is a table called LOCATIONS with INT Identity as the key. My concern is over all the other tables involved:1) Is an INT Identity field the best choice for the sole primary key for Customers, Vendors, Products, etc. with LOCATION_ID just being a foreign key into the LOCATION table?2) We in the past used Vendor Number, Customer Number, Product Number alphanumeric fields as keys in the COBOL Vision files. If we use an INT Identity field as the key, is it sufficient to simply require the combination of VENDOR_NUMBER and LOCATION_ID to have the unique constraint or would this combination be the primary key instead of using INT Identity?These questions are basic, but will affect almost every table in our database simply because we seperate the data by location.Any suggestions on primary keys, index considerations, etc. Any advise for this first time database designer would be appreciated!ex. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-05-01 : 13:14:47
|
| Your approach sounds good. Make sure you use identity columns for those INT PKs so that SQL Server auto-generates the values for you and you don't have to maintain them. You could also choose to use the natural key as the primary key, but most people believe that identities are best. You'd want to use a unique constraint over the natural key if you go with an identity PK.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
|
|
|
|
|
Primary Key / Index Kickstart