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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-10 : 08:14:13
|
| Bob writes "Hello,This is a GREAT web site! So much info! I have been reading several articles in search of answers to a designing/programming dilemma that I have with building an accounting database. (brief background):Our company currently has a legacy accounting system. We are provided a dump of the data to upload into our SQL server. This database is mainly to provide custom and unique reporting to our user community but, I plan to use it for data entry and other functionality that our legacy system does not provide (ie, control).The initial design takes information from a legacy and breaks it out into several entities (7 entities, normalized). The primary key for this is an "accounting code", which is made up of 3 columns: company, department, and natural account. The 3 columns are also primary keys in their respective entities. The 4th entity is the "main account entity" that stores the "accounting code". Other entities store balances, transactions, budgets.(Problem):The problem is that the programmers do not like the "accounting code" because it is too long. "Accounting code" is a minimum of 6 and max. 12 numeric digits (mostly 12 digits). Programmers want to use the Identity column and they would use programming to control referential integrity. Programmers say the Identity column will improve performance. With the Identity column I lose a "meaningful" column(s) that builds the relationships between all of the entities. (millions of records are involved!).I found your articles helpful (Surrogate key vs. Natural Key), and (Identity and Primary Keys)..and others, but I'm just not sure what is best. It's difficult to find samples of accounting database designs. I just see a bad design and later trouble using the identity column as the primary key.(2-part Question):If Identity column is the best way, how can I validate (to boss) that I'm effectively going aqainst normalization, and referetial integrity rules?If "accounting code" or something like it is best, how do I validate it to the programmers?Thanks again. this is with out a doubt one of the best SQL resource sites I have found." |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-06-10 : 09:55:54
|
| I take it the key in question is going to be used as foreign keys in other tables ?The key you are talking about is a compound key - very inefficient for supporting joins between tables.Using an INT column (IDENTITY) will give you a surrogate key that will support the joins much faster and your indices to support those joins will be smaller. Listen to your programmers, sounds like they know what they're talking about.Normalisation rules can be broken - simple. The reason ? PERFORMANCE. |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-06-10 : 10:10:54
|
| I regularly use Identity fields to aid performance an use these to link tables.But to reduce confusion I add a prefix an suffix to it for the user interface.Example A Board PO for KL would Look like. B1234KL. JimUsers <> Logic |
|
|
|
|
|
|
|
|
Dilemma with identity key over unique columns