MS SQL: One Large, Multi-Column dbo.Person table or Multiple Subsets With Multiple Joins?

Question

Hi,

I am thinking through the layout of my "Person" table in my MSSQL DB. Is it better to have all columns in dbo.Person as such:

Person
(
personid
first
last 
email
confirmcode
isconfirmed
homeaddress
homecity
homestate
homezip
session
ipaddress
workaddress
workcity
workstate
workzip
etc...
)

OR, is it better to partition the table into related tables like so:

Person
(
personid,
first,
last
)

Email
(
emailid,
personid,
email,
confirmcode,
isconfirmed,
modifydate,
createdate
)

Session
(
sessionid,
personid,
session,
activitydate
)

HomeAddress
(
homeaddressid,
personid,
address,
city,
state,
zip
)

WorkAddress
(
workaddressid,
personid,
address,
city,
state,
zip
)

I have read arguments for both. The former (one table) says that performance takes a hit because of the need for multiple joins when returning data. I have also read that having multiple tables reduces future fires when you have to add or remove new columns related to a given grouping (for example, adding an alternate email address will create NULLs in your Person table).

Thoughts? Thanks.

Answer 1

One case to consider is that your second option is a tad more extensible as you're not restricted to 1:1 relationships, so one person can have multiple Emails (1:N relationship between person and email), sessions or addresses.

In such a case I'd consider modifying the Address table to be as such

Address (
  addressid,
  personid,
  addresstype
  address,
  city,
  state,
  zip
)

where addresstype could be

• work
• home
• temporary
• previous

which would save having two tables (WorkAddress and HomeAddress containing the same kind of data), but as I say, it depends on how complex your model is going to be. For example, a N:N relationship between address might be more appropriate, i.e.

Address (
  addressid,
  address,
  city,
  state,
  zip
)

Address_Person (
  addressid,
  personid
  addresstype
)

would then allow for the fact that multiple persons can live at the same address.