[MySQL]: Is there a more 'efficient' way to link these tables?

Question

I have three tables -

Transaction:
ID    TimeStamp

Discount:
Code   Amount

Transaction_Discount:
ID      Code

It's almost as basic as you can get.

Is there a more 'efficient' way to link these tables together?

Answer 1

If you are modelling a transaction can have 0 or more discounts and a discount can be applied to 0 or more transactions then this 3 table approach is the standard way to go.

The only change I would make would be to give discount a numeric primary key in addition to the code field and use this as the foreign key in Transaction_Discount.

Answer 2

If it is one to one, then just add a discount code field to your transaction table.

If it is one to many, then add a transaction id to your discount table.

If it is many to many, then the three table solution you have is the only route to go.

Answer 3

From your comment, this relationship is a one to many where one discount can be applied to many transactions.

Therefore, you should model your tables as follows:

Transaction table:
id, discount_id, timestamp

Discount table:
id, code, amount

As you can see there is no need for the 3rd link table. This would only be needed if the relationship was many-to-many.

You can also see, that as a transaction only has one discount, we store the discount ID in the transaction table as a foreign key. This way we can have many transactions referencing the same discount if we want but each transaction will only ever reference one discount.