Design/load question

Hi. I am redesigning a database to be more normalized. There are 4 columns that appear in many of the tables that I plan to add to a new table. Here is what the database looks like now:

table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.

table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.

table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
samecolumn1
samecolumn2
samecolumn3
samecolumn4
uniquecolumn4
etc.

If I add a 4th table with the columns that are the same in each of the tables, I'd have this:

table1
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.

table2
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.

table3
uniquecolumn1
uniquecolumn2
uniquecolumn3
uniquecolumn4
etc.

table4
samecolumn1
samecolumn2
samecolumn3
samecolumn4

To link table4 to the other tables, I'd define foreign keys on table4 that reference the primary keys of tables 1-3. What I'd like to avoid is having a composite key on table4 made up of the primary keys of tables 1-3. That doesn't sound correct. How do I do this? Also when it comes time to load data, I know that I need to populate tables 1-3 first, but how do I then uniquely identify the rows in tables 1-3 that must go into table4? Thanks for the help!

Byron,

Could you please be more specific on the column names for each table?

Steve

|||Steve,

Thanks for the response. Here's what you asked for:

LodgingSummary (table1)
LodgingSummaryID
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses

CardTransaction (table2)
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
Period
AcquiringBIN
CardAcceptorID
SupplierName
SupplierCity
SupplierStateProvinceCode

FleetService (table3)
FleetServiceID
LoadTransactionCode
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
PurchaseType
FuelType
FuelUnitMeasureCode
FuelQuantity
FuelUnitCost
FuelGrossAmount

AccountInfo (table4)
AccountNumber

PostingDate

TransactionReferenceNumber

SequenceNumber

|||

Byron,

CREATE TABLE AccountInfo
(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)

CREATE TABLE CardTransaction
(
LoadTransactionCode (PK)
LodgingSummaryID(FK)--allow nulls
FleetServiceID (FK) --allow nulls
Period
AcquiringBIN
CardAcceptorID
SupplierName
SupplierCity
SupplierStateProvinceCode
)

CREATE TABLE LodgingSummary
(
LodgingSummaryID (PK)
AccountNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses
)

CREATE TABLE FleetService
(
FleetServiceID (PK)
PurchaseType
FuelType
FuelUnitMeasureCode
FuelQuantity
FuelUnitCost
FuelGrossAmount
)

|||

CREATE TABLE AccountInfo
(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)

If you do this, then that person can never use this account again. Better off making a surrogate for this data:

CREATE TABLE AccountInfo
(
AccountInfoId int identity(1,1) primary key, --I don't care about the value of this, it is just a surrogate for:

AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)
)

I find that I am a bit wary of the data here. Is AcctNumber and TransactionRefNumber not unique in and of itself? Same with it and SequenceNumber. Make sure that your alt key(s) really define with is actually unique, and not just a blob like this (unless the same tranNumber and sequenceNumber might be repeated for the same account, but just on different days.

I am rarely happy when a date is part of a key that is not representing an Event of some sort (like if I had a table that recorded when I got gas for my car, it would have a date as the key.) I don't know your data, so I am not sure of course :)

|||Thanks for the response, Louis. I added my responses is blue.

"CREATE TABLE AccountInfo

(
AccountNumber (PK)
PostingDate
TransactionReferenceNumber
SequenceNumber
)

If you do this, then that person can never use this account again. Better off making a surrogate for this data:"

This is correct. There will be multiple transactions for the same account number.

"CREATE TABLE AccountInfo
(
AccountInfoId int identity(1,1) primary key, --I don't care about the value of this, it is just a surrogate for:

AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)
)

I find that I am a bit wary of the data here. Is AcctNumber and

TransactionRefNumber not unique in and of itself? Same with it and

SequenceNumber. Make sure that your alt key(s) really define with is

actually unique, and not just a blob like this (unless the same

tranNumber and sequenceNumber might be repeated for the same account,

but just on different days.

I am rarely happy when a date is part of a key that is not

representing an Event of some sort (like if I had a table that recorded

when I got gas for my car, it would have a date as the key.) I don't

know your data, so I am not sure of course :)"

There are many more tables that could have transactions related to travel, lodging, purchases, etc. These are all in different tables according to the data spec I have to live with. TransactionRefNumber should be unique, but there is no guarantee. When you said:

"UNIQUE (accountNumber, PostingDate, TransactionReferenceNumber, SequenceNumber)"

What is this? Are you creating a unique constraint here? Thanks.|||One more thing, please. Now when I want to load my values into my newly normalized table (LodgingSummary), how do I select each row individually? LodgingSummary will have the primary key of AccountInfo as a foreign key. Let's look at a real table:

Table: LodgingSummary (the newly normalized table)
LodgingSummaryID (PK)
AccountInfoID (FK) <-- This is the primary key of AccountInfo
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses

Table: AccountInfo (the table I'm "normalizing" to)
AccountInfoID (PK)
AccountNumber <--moved from tbl LodgingSummary to tbl AccountInfo
PostingDate <--moved from tbl LodgingSummary to tbl AccountInfo
TransactionReferenceNumber <--moved from tbl LodgingSummary to tbl AccountInfo
SequenceNumber <--moved from tbl LodgingSummary to tbl AccountInfo

After the load, there will be a 1:1 relationship between the rows in the two tables (primary key to foreign key). My datasource for the load is the original de-normalized table:

Table load_LodgingSummary
LodgingSummaryID (PK)
AccountNumber
PostingDate
TransactionReferenceNumber
SequenceNumber
NoShowIndicator
CheckInDate
DailyRoomRate
TotalOtherCharges
TotalTaxAmount
TotalFoodBeverageCharges
TotalPrepaidExpenses

Here's my current code to load the normalized table:

[CODE]
set identity_insert lodgingsummary on
insert into lodgingsummary
(
LodgingSummaryID,
AccountInfoID,
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges,
TotalTaxAmount,
TotalFoodBeverageCharges,
TotalPrepaidExpenses
)
select
LodgingSummaryID,
(select ai.accountinfoID AccountInfoID from accountinfo ai,
load_LodgingSummary ll
where ai.accountnumber=ll.accountnumber and ai.postingdate=ll.postingdate
and
ai.TransactionReferenceNumber=ll.TransactionReferenceNumber
and ai.sequencenumber=ll.sequencenumber),
LoadTransactionCode,
NoShowIndicator,
CheckInDate,
DailyRoomRate,
TotalOtherCharges,
TotalTaxAmount,
TotalFoodBeverageCharges,
TotalPrepaidExpenses
from load_lodgingsummary
set identity_insert lodgingsummary off
[/CODE]

The subquery in the INSERT will return more than 1 AccountInfoID. Is there any way to write a statement that will load each row individually or do I need to build a recordset and individually insert each row of the recordset?