Mysql – How to design address table

database-designMySQLnormalization

Hello I need designing addresses table. I designed it but I didn't like it very much.

My needs:

  • I have customers and users (admins). I have addresses for customers and users. We need design in different tables like CustomerAddresses and UserAddresses. But we must be have Addresses table and connect with CustomerAddreses and UserAddresses.
  • I have different address types like individual, corporate address. This types just using in customer's addresses.
  • A customer can have more than one address.
  • A user can have more than one address.
  • Customers are not individual or corporate. Customer addresses are individual or corporate.

My design: (but I think this design not good. I think this design breaking normalization rules.)
CustomerAddresses is junction table in this design. And belongs to CustomerAddressTypes. We can create a new design or update my design.

https://dbdiagram.io/d/5fc2218b3a78976d7b7dbaf6

enter image description here

Best Answer

From my first run through on this, I would take an Object Oriented Programming eye to it. The Users and Customers tables are extremely similar, so I would probally make a single table with all the fields, and add in an additional field designating the Person as either a customer or admin. This would also remove the need for having separate join tables for addresses.

In dealing with the additional information needed for corporate addresses, I would look into adding a type field into the addresses table to denote if its a corporate customer. Then add a trigger that fires when the address type is corporate that would insert the tax information into a separate table like you already have.

Also, noticing that all three address tables contain some kind of name field, I would FK it into the Persons table, since every address would have some kind of either admin or customer already. These can be used for the corporate types as a Point of Contact name. my goal is to avoid having nearly identical tables, and avoid having the same information (like names) be recorded in multiple tables.

Addresses
    Address_ID 
    Street_Address 
    City_ID 
    Address_Type --Personal\Corporate
    Notes 
Persons
    Person_ID 
    First_Name 
    Last_Name
    Email
    Password
    Phone
    Person_Type     --Customer\Admin
Person_Address_Join
    Join_ID
    Person_ID  --FK to Persons
    Address_ID --FK to Addresses
    Notes
CorporateInfo
    Info_ID
    Address_ID  --FK to Addresses
    Tax_ID
    Tax_Office