May 22, 2009 at 3:25 pm
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 NULL
CONSTRAINT [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] NULL
CONSTRAINT [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) NULL
CONSTRAINT [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) NULL
CONSTRAINT [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
May 22, 2009 at 3:44 pm
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) Berzukov
Author 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.May 22, 2009 at 3:58 pm
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
May 23, 2009 at 6:36 am
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) Berzukov
Author 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.May 23, 2009 at 6:46 am
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