Mysql – Feedback on an entity-relationship diagram for a movie theater business domain

database-diagramserdMySQL

I have an assignment due next week, and for that assignment I am supposed to learn MySQL and create an entity-relationship diagram (ERD).

My current attempt

Now I've made this ERD

enter image description here

The issue

I went to my professor for feedback and he just told me that it is not a good ERD and refused to give me any further feedback.

I'm still a noob, but my question to you is: How do I improve this ERD? I was thinking about removing the Ticket entity type since that might be confusing…

Best Answer

The following are examples used to convey the point, there are multiple instances of each of these examples within your ERD.

Naming

Consistency is key. Some table names are pluralised (Seats) some are not (Ticket). Your table contains data about many tickets so I normally prefer pluralised table names.

You're prefixing every column with the name of the table. This makes for column names like staff_staff_id, which isn't instantly understandable to readers. If I named the primary key of Staff ID then you store staff.id and you might access tickets.staff_id, which makes everything more understandable.

Relations

There's a couple of relations that don't exist, for example you've got seat_id in Ticket (makes sense if you have preassigned seating) but no relation to Seats.seats_id? Why not? If your cinema/theatre doesn't have preassigned seating then you don't need this data.

There are some duplicates within tables. Why does Ticket contain both staff_id and staff_staff_id?

Data duplication

There are some unnecessary (unenforced) relations that are allowing for data inconsistencies. If a MovieTime must happen within a Hall an a ticket must be at a MovieTime then there's no need for Ticket to contain any information about the hall in which the movie is going to be shown.

It's unclear what the difference between the time_of_screening and screening_time is. Maybe one of them is the number of minutes the screening runs for? This information is already within Movie and so is another duplication.

Data

There's no need to persist the age as you've already got the date of birth. I've written about this on Stack Overflow a few times.

It's quite an assumption that all staff and movie names will be 45 characters or less.

I find the inclusion of sex in the staff table a little off-putting. What's the purpose of storing this information? Are the chromosomes of the person selling you a movie ticket that important in this cinema/theatre. I also don't understand why it's 10 characters long.

How to start

Think about exactly what information is required to go within each table. If you're not sure it's required then don't add it in. If you need to add something later then so be it, but you haven't included a lot of unnecessary data. I'd set-up some test tables with dummy data to see if what you're creating will actually work.

Every relation to another table should be enforced. If you've got some unenforced relations then either add a foreign key constraint or remove the column.

Your cinema probably has tickets, so removing the data will prevent you selling tickets.

Related Question