July 7, 2004 at 3:35 pm
Hello,
I am designing a db app where I have to deal with 16 diff transactions each with different fields. they all have a set of data that is common to all the transactions. So I have one table with all the transactions with a transaction type and the common fields. and I have transaction specific tables with uncommon fields as shown below:
tblTranCommon:
RegionID
MobileID
AccID
Year
Period
TranDate
TranType ..(transaction)
CustomerName
tblTranAct:
MobileID
ActDate
ContractTerm
Plan
...
tblTranUpg:
MobileID
UpgDate
LastActDate
OldPlan
NewPlan
...
In the tblTranCommon I can combine mobileID,AccID,RegionID,TranType to make unique constraint but in the other tables I can not implement the same constraint as I do not have those fields there. I did not put the constraint fields in the other tables because we deal with millions of data and I dont want to duplicate any data. How can I implement the same constraints on the other tables?
July 8, 2004 at 5:02 am
As I understand it, you want to have in the tblTranAct table only rows that have a corresponding row in tblTranCommon, i.e. it's not possible to have a tblTranAct row (or a tblTranUpg row, also) if there is no corresponding row in tblTranCommon. If that's the case, you should have a foreign key in tblTranAct that reffers to the primary key of tblTranCommon. If the unique key in tblTranCommon is a multi-column key, you could make another column, let's call it TransID that has the "identity" property set, and use this one as a foreign key in the other tables.
If the data in the other tables should also be unique (i.e. you don't want to have two rows in tblTranAct that reffer to the same row in tblTranCommon), then you could make this column (TransID) to be also the primary key (not only a foreign key) in the tblTransAct table.
Razvan
July 8, 2004 at 7:06 am
Razvan,
This sounds like a cleaner solution then what I was thinking of. But how would I create the same identity key in both tables? I am just doing a reguler insert. the constraints keep out the dupes.
Thanks for your help.
July 8, 2004 at 8:41 am
The TransID column will be an identity column only in the tblTranCommon table; in the tblTranAct table (and the other tables), TransID will be a normal (int) column. The application that inserts the data in the tables should get the last value of the identity column using the SCOPE_IDENTITY() function, like this:
DECLARE @TransID int
INSERT INTO tblTranCommon (RegionID, MobileID, AccID, ...) VALUES (1, 2, 7, ...)
SET @TransID=SCOPE_IDENTITY()
INSERT INTO tblTranAct (TransID, ActDate, ...) VALUES (@TransID, '20040708', ...)
If the application inserts more than one row at a time (for example if you transfer the data using DTS from a text file, or if you use INSERT INTO the_table SELECT ... FROM other_table), the problem gets a little bit more complicated. In this case (after you need insert the data into the tblTranCommon) for inserting the data in tblTranAct table, you need to join the source table with the tblTranCommon table on the other unique key, that is (MobileID, AccID, RegionID, TransType) in your case.
Razvan
July 8, 2004 at 9:03 am
Interesting...I am going to try this. I will let you know how it works out. I am assuming scope_identity is getting the Identity values of TransCommon.
Thanx
July 8, 2004 at 9:11 am
I was just reading up on scope_identity...it says that it returns the last identity value inserted.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp
if thats true then how will I join the tblTransCommon to tblTransAct? Am i missing something?
Thanx
July 8, 2004 at 9:28 am
Sorry for soo many questions. but I was re-reading what you said:
"If the application inserts more than one row at a time (for example if you transfer the data using DTS from a text file, or if you use INSERT INTO the_table SELECT ... FROM other_table), the problem gets a little bit more complicated. In this case (after you need insert the data into the tblTranCommon) for inserting the data in tblTranAct table, you need to join the source table with the tblTranCommon table on the other unique key, that is (MobileID, AccID, RegionID, TransType) in your case."
This is what I am doing but I am still getting the dupes in tblTransAct. I am joining the key fields from Common to the Text file in the insert to tblTransAct.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply