DB designing Help needed

Hi all,
I need to design a database for entire bollywood film industry, main aim is to fetch data by any criteria, such as if we say we need films of this director tehn it shud display the films then who acted at what position etc... Hoping to get a good response .Originally posted by shani
Hi all,
I need to design a database for entire bollywood film industry, main aim is to fetch data by any criteria, such as if we say we need films of this director tehn it shud display the films then who acted at what position etc... Hoping to get a good response .

Like these guys? http://www.imdb.com

Send $$ to...
How about this...

Production Table - Holds all attributes of the production
------
productionid
name
productiontypeid
plot_outline
tagline

ProductionTypes - Movie, Play, Book, Webcast...
-----
productiontypeid
productiontype

Genres - Comedy, Drama, Crime...
--
genreid
genre

ProductionGenres - Production X is a Crime Comedy...
------
productionid
genreid

Persons - Attributes of anyone involved in a production
---
personid
lastname
firstname

InvolvedPersons - Person X is involved in Prod X as a Director...
-----
personid
productionid
involvement_type_id

InvolvementType - Director, Screenplay Writer, Actor...
-----
involvement_type_id
involvementtype
involvement_category_id

InvolvementCategories - Cast Member, Crew Member, Writing Credits
-------
involvement_category_id
involvement_category|||2 bill_dev:

What about if movie was created by couple productions?

I guess it needs to do some changes:

Artpiece table - names of movies, etc.
-------
ArtpieceId
Productiontypeid -(movie, etc.)
Name

ArtpieceProduction - who did it
--------
ArtpieceId
productionid

InvolvedPersons
------
personid
ArtpieceProductionid - FK to Artpiece
involvement_type_id

and modify another tables accordingly.|||Originally posted by snail
2 bill_dev:

What about if movie was created by couple productions?

I guess it needs to do some changes:

Artpiece table - names of movies, etc.
-------
ArtpieceId
Productiontypeid -(movie, etc.)
Name

ArtpieceProduction - who did it
--------
ArtpieceId
productionid

InvolvedPersons
------
personid
ArtpieceProductionid - FK to Artpiece
involvement_type_id

and modify another tables accordingly.

Right, bad choice of words on my part. Substitue Artpiece everywhere I said "production". So, IMO, you'll want Artpiecetypeid instead of productiontypeid in your Artpiece table.

As far as tracking real movie "production" goes I'm not sure I understand your ArtpieceProduction table. Personally I would put all the production people in the persons and Involvement tables and then put and ID for actual "Production Company" in the persons table which linked to a new "Company" table. Usually folks only have one employer but they can be involved in many ways.

For example, Kevin Coster may be Director, Producer, and Actor for one particular movie so he'd have 3 records in the Involvement table (but I strongly suggest you avoid his movies altogether ;) )

Hope that helps.|||Originally posted by bill_dev
Right, bad choice of words on my part. Substitue Artpiece everywhere I said "production". So, IMO, you'll want Artpiecetypeid instead of productiontypeid in your Artpiece table.

As far as tracking real movie "production" goes I'm not sure I understand your ArtpieceProduction table. Personally I would put all the production people in the persons and Involvement tables and then put and ID for actual "Production Company" in the persons table which linked to a new "Company" table. Usually folks only have one employer but they can be involved in many ways.

For example, Kevin Coster may be Director, Producer, and Actor for one particular movie so he'd have 3 records in the Involvement table (but I strongly suggest you avoid his movies altogether ;) )

Hope that helps.

In this case you do not have information about who created a movie. If you are going to put information about company to table Persons - much better to rename this table for understanding.|||Originally posted by snail
In this case you do not have information about who created a movie. If you are going to put information about company to table Persons - much better to rename this table for understanding.

No, do NOT put company info in person table. I didn't suggest that. I included information about the people that created the movie, not companies that created the movie.

If there are companies that were involved in creating the movie but you don't know the actual people then, yeah, you need more tables than I described...Like "InvolvedCompanies" or something...|||Originally posted by bill_dev
Like these guys? http://www.imdb.com

Send $$ to...
How about this...

Production Table - Holds all attributes of the production
------
productionid
name
productiontypeid
plot_outline
tagline

ProductionTypes - Movie, Play, Book, Webcast...
-----
productiontypeid
productiontype

Genres - Comedy, Drama, Crime...
--
genreid
genre

ProductionGenres - Production X is a Crime Comedy...
------
productionid
genreid

Persons - Attributes of anyone involved in a production
---
personid
lastname
firstname

InvolvedPersons - Person X is involved in Prod X as a Director...
-----
personid
productionid
involvement_type_id

InvolvementType - Director, Screenplay Writer, Actor...
-----
involvement_type_id
involvementtype
involvement_category_id

InvolvementCategories - Cast Member, Crew Member, Writing Credits
-------
involvement_category_id
involvement_category

plot_outline (can you tell whats this? i know it woud take text but would it be writer or what??)
tagline (and whats this?)

anyways thanx for this much help as well both of u guys , U look very much into database designs??!!.|||Originally posted by bill_dev
No, do NOT put company info in person table. I didn't suggest that. I included information about the people that created the movie, not companies that created the movie.

If there are companies that were involved in creating the movie but you don't know the actual people then, yeah, you need more tables than I described...Like "InvolvedCompanies" or something...

some how I need to have the movie name as well when I will be displaying the results,

what if the criteria is all movies in this year? or when was this movie released, who was it lyricist, etc or an other...|||Originally posted by shani
some how I need to have the movie name as well when I will be displaying the results,

what if the criteria is all movies in this year? or when was this movie released, who was it lyricist, etc or an other...

I guess now you do have a main idea how to design a database in this case. If you need to keep more information - make some analysis about it, try to find best way to save information (you could read something about database normalization).|||Originally posted by shani
some how I need to have the movie name as well when I will be displaying the results,

what if the criteria is all movies in this year? or when was this movie released, who was it lyricist, etc or an other...

Just to explain some of the fields, I used this page to determine what all the data one might want to store about a movie: http://www.imdb.com/title/tt0325805/