Entity relationship diagram advise needed

Question

I am creating a database based on a ERD i have designed according to some business rules where I am allowed to make assumptions and implement them for the future.

Business rule:

Entity relationship diagram

Based on the business rules the customer is invoiced for the holiday, hence the relationship would be 1..1, however I have been left to assume that the customer may receive one or more invoices for the same reservation, that's if the customer makes changes to the reservation or a reminder invoice is raised.

IF i leave the relationship 1..1 then i might a swell get rid of the invoice table and use the reservation as the invoice since they use the same attributes and link it to the payment_method.

I don't know which way is best, first time doing databases...

Please advise

Answer 1

It almost sounds to me like you should make it a 1 to many relationship between the invoice and the reservation. You say that a customer may receive multiple invoices for a single reservation, such as if the reservation changes. That makes me think that it should be a one reservation to one or more invoices.

What I might include on the invoice table would be a field telling if it is the latest invoice, or a nullable field pointing to the next invoice. If an invoice becomes invalid/outdated/superseded, then a new invoice is created and all previous invoices then have their superseded field filled in to point to the most current invoice. That way you can still keep a trail of previous invoices as well as the current one.