Database design & referential integrity

Question

I am designing a website UI (Signup, signin, edit profile, edit address, etc..)

I have created the following tables. My question is, are there any redundant columns or tables? Also, which columns will be primary key and which will be foreign keys considering the fact that a user may add/edit another address?

I am using three dropdown lists for CountryCode (for mobile no.) - populated from CountryCode of ACountry, Country (for address) - populated from Country Name from ACountry and State (for address) - populated from StateName from Astate. Out of these, the country code dropdown comes on the signup page. The other 2 are on the address book page that is displayed after the user is logged in. The AUser & AUserAddress tables will store the data.

I have no idea how I am going to join these tables. Have I included sufficient/more/less columns? What changes should I make.

UPDATE1: I have updated the database to the following tables/columns. Now how do I establish a relationship between Countrycode & Countrycode (marked)... Or should I drop CountryCode from AUser. If I keep it both the tables I'll be storing data in 2 places. I dont want to do that... The problem is the format of the Countrycode. It takes the name AND the dialing code (e.g. India(+91)). If I drop that column from AUser, how will I store the mobile nos. then? May be I'll have to break the Country code string and remove the dialing code part and store it with the mobile no. in the Mobile column. That sounds too complicated.

Answer 1

The only relationship you haven't got right is between AUser and ACountry. Currently, you have CountryCode on AUser. That should be CountryID instead, as that's the primary key of ACountry.

Also, shouldn't AUserAddress have StateID and not State? If that's the case, there's no need to have both StateID and CountryID on that table, you can determine the Country of a user by looking at the corresponding State referenced in the StateID column.