CODEWITHSUNDEEP
mysqlsqlsql-serverdatabase
MickB
MickB

Reputation: 75

Can a table name be used as a field for another table?

I have three tables Movies, MoviesWatched, Person1....n

Movies table:
M_ID, Title, Director, etc..

MovieWatched table:
M_ID, DateWatched, (Person who watched movie)

Person1...n table:
MovieOwned(Same as M_ID), NumberOfViews, ________

Basically for each user there will be a separate Person table created that holds the MovieIDs that exist in their movie library. I am trying to find a way to link the MovieWatched table to the Person1...n table. So far, the only way I see is to have a P_ID for each movie in the row. I'm looking for another way because that would make it that each Person table row will always contain the same P_ID.

The alternative would be to have one table that holds all persons. The drawback to that would be that the table would need 1...n columns to hold the movieIDs and there would be multiple columns left NULL.

Each Person table will be unique in the tablename itself so I am wondering if it is possible to refer to a table name as a field in MoviesWatched so that I do not have to repeat the PersonID in the rows for each movie owned.

Upvotes: 1

Views: 578

Answers (2)

Dan J
Dan J

Reputation: 16718

You're on the right track with your MoviesWatched table: consider that tables in a relational database can represent entities or relationships.

You're halfway there by referring to Movies by their ID from other tables. You'll want to do the same for Persons:

Movies table:
M_ID, Title, Director, etc..

Person table:
P_ID, Name, Other attributes of an individual person...

MovieWatched table:
M_ID, P_ID, DateWatched

MovieOwned table:
M_ID, P_ID, NumberOfViews

Note the latter two tables. A row in each of those tables represents an instance of a specific person watching or owning a specific movie. Having a relatively small number of rows in the entity tables (representing movies and people) and a relatively large number of rows in the relationship tables (representing each time any person watches or owns any movie) is a natural outcome of the relational model, and database engines like MySQL are designed to support it.

(Another Note: technically, a field like NumberOfViews could be calculated by counting entries in the MoviesWatched table for a given person and movie, and then correlated with whether or not the person also owns the movie, rather than storing the field on MovieOwned and needing to keep it updated over time. That's the sort of power you get from properly using this model!)

Upvotes: 1

Tim Lehner
Tim Lehner

Reputation: 15261

You seem to be describing a many-to-many relationship. This is normally solved using a junction table:

Movies table:
M_ID, Title, Director, etc...

Person table:
P_ID, Name, etc...

MovieWatched table:
M_ID, P_ID, DateWatched

See how we can relate any number of movies to any number of people? Thus, "many-to-many."

You can apply the same concept to movies owned.

I would also put in referential integrity: constraints that the database will enforce on your data. In this case, MovieWatched.M_ID should be a foreign key to Movies.M_ID. Similarly with Person.P_ID. This will keep your database from having orphan records, or records that might exist in MovieWatched, but not in Person or Movies (imagine deleting a person but not their watched movies).

You could also put a unique constraint on MovieWatched (M_ID, P_ID) if you only wanted to allow each person to view each movie once (this often happens in a junction table but may not make sense for you if you essentially want a log of movies watched).

Upvotes: 2

Related Questions