Advise re: DB Design - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Advise re: DB Design

Author

Topic

dreamer_0244
Starting Member

7 Posts

Posted - 2008-10-13 : 23:21:50

I am currently working on this table. It will contain the transactions made. But the transaction varies, it may be a purchase order, transfer from inventory, rental, etc. I have different IDs for each type of transaction (e.g POID, TFID, RENTALID). My question is, should I place all of this fields on my transaction table?

Sample Table Structure:

TransactionID - int

POID - int

TFID - int

RENTALID - int

I am wondering if this is a correct design because if the transaction is a purchase order, the POID will be set, but the TFID and RENTALID will be null. I would appreciate any advice....thanks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 00:33:43

I think you should have a table looking like this

TransactionID INT IDENTITY(1, 1)
TranType SMALLINT -- 1 = poID, 2 = tfID, 3 = rentalID
ExternalID INT -- The actual external (poID, tfID or rentalID)

I believe this design would be more efficient and easier to maintain.
It will be very easy in the future to add a new type.

E 12°55'05.63"
N 56°04'39.26"

dreamer_0244
Starting Member

7 Posts

Posted - 2008-10-14 : 21:05:29

But how would I be able to enforce referential integrity with the ExternalID and its related table. The IDs are integer and are identities so there is a possibility that there will be same ExternalID but they are of different transactions

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 02:27:33

You are right.
But the workaround can be to write a function to do the check, depending on type.

E 12°55'05.63"
N 56°04'39.26"

dreamer_0244
Starting Member

7 Posts

Posted - 2008-10-15 : 04:02:24

Thanks for the tip!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-15 : 04:03:50