Ok so I am designing a database for my website I am making, which will host information about different things, (a bit like IMDb).
I will have 'movie' pages, 'tv' pages & pages for 'actors', 'directors' and possibly writers.
Now I originally came up with something along the lines of this:
Film {
MediaID: #One media type (film/tv) will have one ID
Description: #One film will have one description
Cert.UK:
Cert.US: #One film may have a certificate for each country
IMDb: #One film will have one IMDb rating
Duration: #One film will have one duration
Release: #One film will have on release
Genre: #A movie may belong to multiple genres
People.ActorID #One film may have multiple actors
People.DirectorID #One film may have multiple directors
Image: #One film will have one image
Trailer: #One Film will have one trailer
}
Cert {
UK:
US:
}
People {
PeopleID: #One person will have one ID
Role: #A person may have multiple roles
Film.MediaID: #A person may belong to multiple films
Name: #One person will have one name
DOB: #One person will have one Date of Birth
Description: One person will have one description
}
For the case of MediaID in Film; both Film and Tv tables will share MediaID as their primary keys, and I don't want to make a Media table and add Type:Film/TV as they require different columns leaving alot of unncecesary null data. Is it ok to leave that as is?
Also:
When adding a new film, how can I make sure it's MediaID doesn't already exist?
When there is a one to many relationship do I need to create a new table? For example I thought about creating a roles and genre table but would that be necessary?
