Can there be official guidance or perhaps a threshold to point when it's best practice to utilize a foreign type in a MySQL database?

Suppose you produced a table for movies. One method to get it done would be to integrate producer and director data in to the same table. (movieID, movieName, directorName, producerName).

However, suppose most company directors and producers have done many movies. Will it be better to create two other tables for producers and company directors, and employ an overseas type in the film table?

When will it become best practice to get this done? When most of the company directors and producers are showing up several occasions within the column? Or is it more beneficial practice to train on a foreign key approach in the beginning? Although it appears more effective to utilize a foreign key, additionally, it boosts the complexness from the database.

Then when does the downside between complexity and normalization become worthwhile? I am unsure if there's a threshold or perhaps a certain quantity of cell repetitions that causes it to be more sensible to utilize a foreign key.

I am considering a database that'll be utilized by 100s of customers, many at the same time.

Thank you!

you will find some official recommendations with this. they are known as normal forms, and the concept of putting your database into them is known as normalization: http://en.wikipedia.org/wiki/Database_normalization

for a db class attending college, they'll most likely train you 3nf or bcnf. i have always found individuals methods to be a little heavy-handed, however i have sufficient experience of db design which i find these inquiries to be essentially intuitive at this time...

inside your example, you certainly desire to use foreign key constraints. a many-to-one relationship is better expressed this way. it'll make choosing movies a little reduced, because you will need to perform a join around the 'people' table and also the 'movies' table - possibly many joins based on the number of 'people' fields the films table has.

however the advantage is you can easily manage the folks themselves. if you wish to alter the spelling of the person title, it's not necessary to scan the entire table searching for that individual in every area. you are able to avoid getting exactly the same part of the db several occasions with slight variations in spelling. you are able to set things to do if an individual is erased. it is simple to count the number of different roles an individual has had.

remember, if you wish to use foreign secrets, you have to build your tables innodb in mysql.

Suppose you produced a table for movies. One method to get it done would be to integrate producer and director data in to the same table. (movieID, movieName, directorName, producerName).

That's way too unnormalized. You are repeating data.

However, suppose most company directors and producers have done many movies. Will it be better to create two other tables for producers and company directors, and employ an overseas type in the film table?

Also suppose a thief may go on a single movie like a producer and subsequently like a director. An individual may be credited as director, producer, author and actor on one movie!

When will it become best practice to do that? When most of the company directors and producers are showing up several occasions within the column? Or is it more beneficial practice to train on a foreign key approach in the beginning? Although it appears more effective to utilize a foreign key, additionally, it boosts the complexness from the database.

You will need to master foreign secrets, associations (especially 1-to-many and several-to-many), and normal forms immediately. They'll become second character very quickly.