Search code examples
mysqldatabasedatabase-normalization

Normalization of an address table advice needed

I have a database with several tables

Student
Teacher
Parents
Staff

All need to have a reference to an address table

Address
    door number
    street
    town
    city
    postcode

How can I create a address table with little duplication of data? Is the only way to have separate tables for each?

Solution

  • Have a separate Address table with the following columns

    address_id
door_number
street
town
city
state
country
postcode

    Now in each of the tables - Student , Parent , Teacher , Staff - have an address_id column and create a foreign key to the address_id in the address table.

    Assuming Student and Parent are already related, you can have the address just in the Parents table.