Click here to Skip to main content
14,599,199 members
Rate this:
Please Sign up or sign in to vote.
Let's say that i have two tables . The first is a cinema department table that has the names of every cinema as variables in a column called name . The second is a movie table that has the cinema department that the movie is being played . Let's say it's a column called dept_name .Now let's say that I add a cinema in the first row of name in the cinema table . How can the value of the specific row get transferred to the corresponding value in dept_name ?

What I have tried:

I'm using phpmyadmin to use MySQL database . I have used oracle data modeler to create a relational model so as to transfer the ddl code and create the tables of the database . I don't know what to do from now on .
Posted
Updated 6-Aug-19 22:31pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Think about your data: unless you have information that links department name to the cinema, you can't "backtrack" from the movie to the cinema via it's department name.

I'd create three tables rather than two:
Cinemas
ID, Name, Address, BillingAddress, ...

Departments
ID, CinemaID, Name, SeatingCapacity, ...

Movies
ID, Name, Genre, StartDate, EndDate, ShowTime, DepartmentID, ...
And the xxxIDs are FOREIGN KEYs back to the relevant table ID.

Then it's a simple matter to use SQL Joins[^] to fetch information as needed, without duplicating any information.
   
Comments
Member 14549747 7-Aug-19 5:03am
   
I have the information that one cinema department has a specific movie number . Joins seem like an interesting outtake.
OriginalGriff 7-Aug-19 6:39am
   
It may do today - but will it show the same movie next week?
JOINs make life simpler, because they are what SQL Server and MySql are all about: they are "relational databases" which means they care a lot about the relationship between bits of data and are very, very good at processing them. Trust me, using JOINs is simple, and reduces the amount of duplication significantly. And that means it easier to keep your data tidy and accurate - called "data integrity" which you do devoutly want as it quickly becomes a nightmare to sort out when DI gets compromised! Duplicated data easily leads to data which doesn't match other rows or tables, and then DI starts to unravel quickly.

Using FOREIGN KEYS and JOINS means it's much, much harder to get bad data in there, and that's a damn good thing as you will undoubtedly be the poor sod who has to manually sort out the cockups ... :laugh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100