DataBase Design for large traffic Website above million rows
Hi,
i am designing a DataBase for my Movie Website hosted on Godaddy shared hosting with PHP+Mysql.
I have read a lot on DB normalization, and DB performance/Scalability, but couldn't find the answer to my current need.
i am starting my website from scratch, so Zero users today, i will be importing 1million Movie entry to the DB. then the users can send me XLS or CSV templates that i will insert thru a script.
so by default all the users on the website are in Read only mode.
here is the current design of my DB tables:
table_name:
id - int
name - var char(45)
table_production_year:
id
year
table_gender:
id
gender
table_rate:
id
rate
table_producer:
id
producer
table_actor:
id
actor
etc...
it is a fully normalized design (i think its 4NF)
now, in the Table_name table, i will have above 1M row.
while in table_gender i will only have few.
My PHP website, will enable users to lookup for Movie names, and see all the associated tags.
but i want them also to be able to lookup by year (say 2012, the movies of this year), or by actor.
here is my questions:
1-isn't that gonna be a messy PHP code, and DB intensive query, especially when i just want to list a simple content such as "all the info of the movies in 2012"?
2- for a simple page view,such as list of movies of category W, i would need to query all the tables!! how should i optimize my queries? Should i create a View?
3-how should i optimize my Design and queries considering the fact i want them to be able to lookup by movie-prodcution-year, and by actor?
now to make the exercise more complex, i need to put a table like:
table_price
id
price
table_currency_exchange_rate
from
to
multiply_factor
so my new questions are:
4-how should i proceed with the design of this part, where i want to list the price of the dvd in multiple currency?
5-let's say the currency exchange rate does not change neither, 1$=1.3euro. how should i proceed with this part? i immagine that i need to store all the prices in the table_price in USD. and then when the user want to list the movie prices in euros, or yen i need to calculate it on the fly? what if i have a lot of users in europe (and in the us), and they always want to lookup for the 10euro movies (and 10$ movies). again i am considering the fact that the exchange rate doesn't change.
should i make my table_price look like this:
table_price_forBetter_performance
id
price_USD
price_EUR
i am going to have millions of Movies so expect million of row.
last question:
6-let's say i use this last design, and that two movies have the same price. should i use the same id(one entry for both, or two entries same)?! the price of one movie might change over time, should i detect in my php code that the is is being used for an other entry, thus i need to create a new one for this?
if you geeks can answer my 6 answers, that would be great.
thanks.