Data warehouse design forum like meeting site

Question

I finished my meeting site design and now I should create database. But I am not sure which method to use.I am using Apache, PHP and MySQL. I have users, each user has input and output mail box and each user has a friends list. This is the basic scenario. And here I have three questions.

• For in box and out box should I create a new table for each person? Or will 1 big in box and out box table and identifying for each mail who is sender and receiver be enough? If there will be 1 million message for each user searching and showing his mail boxes will cause a slow down?

• For creating friend list , also creating one table that stores all both user relationship as a one row data who is a friend with whom ? also searching table for each user friends will cause slow down?

• And I am using Mysql, but says that Oracle most power full. For these reason which one should I choose?

Maybe you can think how many person should register to my site and force to database but who knows ))

Answer 1

I'll take a couple of your questions:

For in box and out box should I create for each person new table? or 1 big in box and out box table and identifying for each mail who is sender and receiver will be enough?

Use one table. This has many advantages, such as being able to easily query all users' messages in a single query. For example to search for all users that have sent more than x messages you can use GROUP BY(userid) and COUNT each group. If you have all the data in one table then it is simple, but if you have a table for each user you will have to generate the SQL for this query dynamically.

If the table grows too large you can partition it, for example you could partition on userid, or year of post, or whatever else makes sense for your data distribution.

If there will be 1 million message for each user searching and showing his mail boxes will cause a slow down?

It depends. If you index the table and search using the indexes then it will be fine. If you write LIKE '%foo%' then it will be slow. Make sure that all your searches use indexes.

I'll leave the other questions for other people to answer.

Answer 2

I think you need to firstly identify all the information you want in the database (names, email addresses, mail messages...) and apply the rules of normalisation to identify your entities and attributes.

Data modeling tools such as Oracle's SQL Developer Data Modeler can help progress that into an implementation.

Don't be scared of 'large' data volumes. Databases cope very well with tables of millions of rows (and orders of magnitude above that).

Oracle vs MySQL depends a lot of money and implementation. You'll find a lot of cheap mySQL hosting. Oracle will be trickier to find and more expensive.