DataBase Design for large traffic Website above million rows
Posted by: James Zimmmer
Date: April 25, 2012 07:01AM

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.

Options: ReplyQuote


Subject
Written By
Posted
DataBase Design for large traffic Website above million rows
April 25, 2012 07:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.