CODEWITHSUNDEEP
sqldatabaseforeign-keysentity-relationship
Sandeep Bansal
Sandeep Bansal

Reputation: 6394

Help with database design

enter image description here

Can anyone help me out here.

I have the Show and Ticket Tables but I'm confused on how I should link them.

My main idea was to have a table full of different ticket types, but will need the ticket type for a specific show. But the Show will need ticket information to know what ticket is selected for the show.

Which table should the foreign key be present in the relationship?

Thanks.

Upvotes: 0

Views: 421

Answers (2)

John Hartsock
John Hartsock

Reputation: 86862

Sounds like for each show you will have many tickets. Therefore the foreign key would belong in the Ticket Table (where show.showid would be the primary key reference)

In generally I think you will have an addition table called tickettypes. This table will act as a lookup for tickets to describe the type of ticket each ticket is.

Here should be your ticket table

Ticket
-------
TicketId
TicketTypeID --Foreign Key to TicketType Table
ShowID --Foreign Key to Show Table
.... Any Additional Fields needed

I also want to note that you seem to be tracking tickets by groups hence the need for a ticketQuantity Field. If you are doing this is it true that each group of tickets is the same price and Type. If this is not true then you should modify your Data model to handle this scenario.

Ticket
------
TicketID
TicketTypeID
ShowID
TicketPrice
OrderID -- would be a foreign key reference linking each individual ticket with an order and total price.

Upvotes: 1

John
John

Reputation: 16007

I'm guessing if a User has a Booking then they're allowed to see a particular Show if they have a Ticket.

In that case, ticketID isn't needed in Show but Booking.

As others have said, TicketType may be a table you want. This might depend not only on Show but the venue. You might want a table for that, too, so that you can abstract out seating capacity for different levels of seating.

Upvotes: 1

Related Questions