Help Critique my DB Schema?

[eluser]Aaron Reisman[/eluser]
Hi there,

so I've got a DB that I'm unsure how well it will preform under a lot of data.

so, I'm going to post it here and hopefully some of you can tell me the reasons why its a good or bad DB Schema.

link to picture of db_schema: http://dl.dropbox.com/u/680559/db_schema.png


anyway, if you can just take a look at that and comment on whats in the key of that picture, that'd be great!

Thanks!

[eluser]danmontgomery[/eluser]
Why do cities, countries, states and zips each need their own tables linked to the user table? Are these supposed to represent multiple addresses? They don't retain any relationship to each other in this way

[eluser]Aaron Reisman[/eluser]
do you think it would be better to put all that info into 1 table?

its in regards to 1 user. so I guess it'd be a better solution.

[eluser]Aaron Reisman[/eluser]
the only question that arises if I did that is how would I be able to count how many users were from the USA or from CA?

-Aaron

[eluser]danmontgomery[/eluser]
If the users need multiple addresses, there should be one "addresses" table with a relationship back to the users. The way you have it now you can have a bunch of cities and countries and states and zips, but they have no meaning other than being attached to a user. For example. I could live in California, USA and also in Barcelona, Spain. In your current table definitions, I might live in California, Spain, or Barcelona, USA, you have no way of knowing... This information is useless.

For your second question, you would combine some very simple SQL with CI's num_rows():

Code:

SELECT DISTINCT `user_id` FROM `twilio_addresses` WHERE `state` = 'California';
SELECT DISTINCT `user_id` FROM `twilio_addresses` WHERE `country` = 'USA';
SELECT DISTINCT `user_id` FROM `twilio_addresses` WHERE `state` = 'California' OR `country` = 'USA';

etc

[eluser]Aaron Reisman[/eluser]
Alright, and can you answer any of the questions I posted in the DB Schema Image?