Design decision: is it a private key or foreign key?
-
I have been importing fixed width file structure of multiple different record types into a number of sql server tables.
The data represents customers' statements with multiple accounts per statement and also transactional level data.
<statement header record 1>
<account header record 1>
<account history1>
<account history 2>
<account history 3>
<account trailer>
<account header record 2>
...
<statement trailer record 1>
<statement header record 2>
...
<statement trailer record 1>
I have parsed the source data into sql and have created tables for each of the record types:
CREATE TABLE [dbo].[StatementHeader]([StatementID] [uniqueidentifier] NOT NULL,[RecordType] [varchar](2) NOT NULL,[FileID] [uniqueidentifier] NOT NULLCONSTRAINT [PK_StatementHeader] PRIMARY KEY CLUSTERED(StatementID ASC)CREATE TABLE [dbo].[AccountHeader]([AccountID] [uniqueidentifier] NOT NULL,[AccountNumber] [varchar](12) NULL,[BeginningDate] [datetime] NULL,[EndingDate] [datetime] NULL,[BeginningBalance] [decimal](14, 2) NULL,[EndingBalance] [decimal](14, 2) NULL,[ProductDescription] [varchar](40) NULL,[StatementID] [uniqueidentifier] NULLCONSTRAINT [PK_AccountHeader] PRIMARY KEY CLUSTERED(AccountID ASC)CREATE TABLE [dbo].[HistoryRecord]([AccountID] [uniqueidentifier] NOT NULL,[Order] [int] NOT NULL,[TransactionDate] [datetime] NULL,[TransactionAmount] [decimal](14, 2) NULL,[TransactionDescription] [varchar](250) NULL,[EndingBalance] [decimal](14, 2) NULLCONSTRAINT [PK_HistoryRecord] PRIMARY KEY CLUSTERED([AccountID] ASC, [Order] ASC)CREATE TABLE [dbo].[AccountTrailer]([AccountID] [uniqueidentifier] NOT NULL,[TotalDebits] [decimal](14, 2) NULL,[TotalCredits] [decimal](14, 2) NULL,CONSTRAINT [PK_AccountTrailer] PRIMARY KEY CLUSTERED([AccountID] ASC)CREATE TABLE [dbo].[StatementTrailer]([StatementID] [uniqueidentifier] NOT NULL,[TotalBalance] [decimal](14, 2) NULL,[TaxInterest] [decimal](14, 2) NULLCONSTRAINT [PK_StatementTrailer] PRIMARY KEY CLUSTERED([StatementID] ASC)Here is where my question comes in — you will notice that I have AccountID as primary key on both account header and account trailer, is this good practice, or should it be primary key on header and a foreign key on the trailer? I have done the same for statement header and trailer?
Just interested in what you guys think on this!
Cheers
Allister
-
I think it goes a little deeper than that -looking at the columns defined in account-header and account-trailer tables I can see no reason to have account-header and account-trailer data in different tables.
As a rule of thumbs... if you bump into two related tables that appear to have a 1-to-1 relationship; fusion both sets of data into a single table.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Thanks Paul, I did consider that, but I thought there was some mileage in maintaining the data's structure as supplied to me, rather than post-normalising (?) the data.
Perhaps I should bite the bullet and (re) merge the 1-to-1 relations, but I have learned never to trust data as supplied: separate tables allows for some sanity checking on the data import.
Allister
-
Allister --
I agree with you provided current table structure is considered a staging area to help dataload -I would certainly get rid of 1-1 relationships on the actual database.
Hope that helps.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Cheers Paul, thanks for your help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply