Mysql – How to design Address table with different types


I have a Address table and different address types like Personal and Corporate. I design a table but I think is breaking normalization rules. How can I improve this design?

My design:
enter image description here

Best Answer

Your design appears to be pretty good overall, but I would make the following recommendations:

  • You already store the client's First Name and Last Name in the clients table, so you can probably remove the client_address_personal_info table altogether. This will reduce redundant data and lead to increased performance and easier management and reporting.
  • You can probably normalize the name of the client_address_corporate_info table to something similar as your clients table, e.g. corporations
  • Similarly to the first point, you should either remove the phone_number field from the clients table or the client_addresses table so it's only stored in one place. (Your choice which table you want to keep it in, I've seen it done both ways.)
  • One other general recommendation (though this is tangentially related to normalization) is you should size your varchar fields to something that is capable of fitting all potential future values. While obviously we can't predict the future, my point is generally leave enough space for larger values that are logically possible. VARCHAR(1) vs VARCHAR(1000) isn't going to affect performance or disk space is any measurably different way. So while most First Names or Business Names can fit inside a VARCHAR(16) field, I'm sure there's names out there that are 25+ characters long. My general starting point on something like that is VARCHAR(50) (and only in rare cases do I use a smaller sized VARCHAR).