CODEWITHSUNDEEP
databasedatabase-designrelational-database
Brian Jenkins
Brian Jenkins

Reputation: 437

Separate tables for name and email addresses?

I've been fighting my initial decision since I've began work on my database. I'm debating whether or not I need a separate table for email addresses. My database looks like this:

people(id, first_name, last_name, email)
addresses(id, address, street, city, state, zip, latitude, longitude)
addresses_people(id, person_id, address_id)
phone_numbers(id, person_id, phone_number, type)

I figured I didn't need a separate table for email addresses as I only wanted one per person regardless if they have more. The problem I'm seeming to have though is that some people will NOT have email addresses. Very often I will be storing children in the people table. Now it seems like it would be better design if I put the email addresses in a separate table to avoid the thousands of empty email fields I'll have.

It's a huge hassle to change this now as the app is already in production somewhat, but changing it now as opposed to a year or two from now would be exponentially easier. Is it worth a day or two to change the emails to another table?

Upvotes: 1

Views: 1009

Answers (2)

Shawn
Shawn

Reputation: 9402

I believe there is no need to change it. Having some fields in a row be empty is not a big deal most of the time. In fact, it is common in most databases. The design of the database should depend on the objects you are trying to model primarily and not be concerned with separating things into tables simply for storage reasons unless you have serious storage constraints or other extenuating circumstances.

See Database design - empty fields which also shed a lot of light on your question.

Upvotes: 0

Tomasz Nurkiewicz
Tomasz Nurkiewicz

Reputation: 340713

In my opinion you are over-engineering. Optional email field is fine. Actually having a separate table might introduce much bigger overhead.

The only reason for a separate table is to model 1-N relationship if you expect the user to have more than one e-mail.

Upvotes: 5

Related Questions