-1

I designed a database, my database will manage concert seats planning, the seats inside the hall will be grouped into categories for each concert. I will try to elaborate more here.

We have a hall, concerts will take place in the hall each one with a date, hall have also seats, categories, each category of the hall will have a different price for different concerts, seats will be grouped into a category, the same category can have different seats for different concert, also we can disable/enable seats for a concert

to have different seats for the same category for different concerts, I made a table (category_concert_seat)in which we have 3 foreign key ids id_concert, id_seats, id_category, like that I can change category for a group of seats for given concert. then in the same table, the same category will have another group of seats for another concert

you can see a demo for my application, the application works but I need to have another professional opinion I don't want to go further without been sure that this is correct.

So is what I did by joining the tree table like is it legitimate or should I reconsider it?

you can see the demo here http://blog.yaz-fr.com/canvas.html

the demo in french but it's easy to understand you just chose a concert from the list then you select group of seats then apply category on those seats you can also give the category a price and change its colour

ER diagram

I have been trying to make my question understandable as much as I can I been searching about the matter for 2 weeks, another question on this platform cant give me the answer that I need.

yazraf
  • 35
  • 6
  • 1
    Please read & act on [mcve]. Otherwise we can only give you a generic answer. That just rewrites a textbook. How is this not answered by the intro to design in your textbook? You may be helped by [relationships between 3 entities in ER diagram--is a ternary enough or are 2 binaries also needed?](https://stackoverflow.com/questions/45551388/relationships-between-3-entities-in-er-diagram-is-a-ternary-enough-or-are-2-bin) or anwers it links to or are linked to it. – philipxy Sep 26 '18 at 18:30
  • What you mean may be closer to "legitimate" than to "legal". Not legal suggests that the DBMS is going to throw some kind of error. – Walter Mitty Sep 27 '18 at 17:46

1 Answers1

0

The problem I see here is that you have two different entities that refer to the CategoryConcert combination - one that defines the seats, and another that defines the price. That could lead to anomalies where there are prices defined for categories that have no seats, or seats assigned to categories that have no price.

I think it might be better to define CategoryConcert, which has an attribute of price, and to hang the CategoryConcertSeat link off that.

I say think because this sort of decision really comes down to use cases. But just looking at your ER diagram that's all that springs to mind for me.

TomC
  • 2,759
  • 1
  • 7
  • 16