Can I connect variables from different tables in mysql ?

Question

0.00/5 (No votes)

See more:

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 6-Aug-19 22:11pm

Member 14549747

Updated 6-Aug-19 22:31pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2019-08-06T22:31:00

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.

Posted 6-Aug-19 22:31pm

OriginalGriff

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: