1

I have created Conceptual data model and Physical data model with this information:
Conceptual data model:

Physical data model:

I want 'Purchase' to have many 'Ticket'. For example, something like this:
(PurchaseID, TicketID)
( 1 1 )
( 1 1 )
( 1 2 )
( 1 2 )
This could correspond to 'One purchase with 4 tickets, 2 one kind of tickets and 2 another kind of tickets)
How can I model correctly?

1 Answer 1

0

Have a 1-many linking table

PurchaseTickets (
 PKey INT identity(1,1) primary Key, 
 PurchaseID INT References Purchase(PurchaseId), 
 TicketID INT References Ticket(TicketId)
)

Remove TicketId from Purchase

I recommend changing field "Price" on Ticket to CostPrice (not the same as Purchase.Price)

Don't store duration on Ticket - can be calculated from DATEDIFF(DAY, Start, End) If you have Currency on Purchase.Price, you also need Currency on Ticket, or at least a conversion rate to make same as purchase price... gets hectic - might need a Date, CurrencyFRom, CurrencyTo, XRate reference table with exchange rates over time, linking on... purchasedate.

2
  • Thank you, I tested it now and it works 😊
    – Justas
    Jan 24 at 12:43
  • @Justas - excellent - please accept the answer :)
    – Alocyte
    Jan 24 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.