Help on database design
 

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!

Re: Help on database design
 

You could use one int ID field and a second type field, which would indicate the type of transaction.

Re: Help on database design
 

Just to expand on pritaeas's comment...

Create a number field in the "transaction" table. You will record either a 1, 2 or 3 in the field. A 1 will indicate that the transaction is a purchase order, a 2 will indicate that the transaction is a transfer from inventory and a 3 will indicate a rental. Of course, you can have as many transaction types as you need.

Lastly, you can create a "transaction type" table with an ID field and a text field. The ID field will correspond to the transaction type recorded in the transaction table and the text field will be a description of the type of transaction. This will allow you to use queries to pull in the type of transaction into forms and reports if needed (instead of hard coding it into the database).

Re: Help on database design
 

But with this design, I would not be able to enforce referential integrity since there may be cases that the IDs will be of the same data but different transaction. What do you think?

Re: Help on database design
 

Can you explain what you mean? The referential integrity would still be intact with this method. We are simply creating a foreign key in the transaction table whose parent is the transaction type. Any referential integrity would be handled the same way any other referential integrity is handled in your DBMS.

The transaction type field is not strictly necessary; it is simply used for your reference.

Re: Help on database design
 

Oh I get it. But is this structure acceptable, thinking that if the transaction is purchase order, then POID will have a value but the TFID and RENTALID will be null?

Sample Table Structure:

TransactionID - int

POID - int

TFID - int

RENTALID - int

TransactionTypeID - int

Re: Help on database design
 

Close... here's the table structure:

TransactionID - int
Amount - Currency
TransactionTypeID - int

The TransactionTypeID will either be 1,2,3, etc. (1 for Purchase, 2 for Transaction, 3 for Rental, etc.)

Does that make sense?

Re: Help on database design
 

yup. thanks!