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

I have three tables -

Transaction:
ID    TimeStamp

Discount:
Code   Amount

Transaction_Discount:
ID      Code

It's almost as basic as you c开发者_运维技巧an get.

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

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.

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.

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.