1-1 relationship?

[TravisLaborde]

I'm just wanting to see what others think about a particular database design issue.

I have a table, "contacts" which contains a record for every "contact." Pretty simple, with basic fields for name, birthdate, etc. With an IDENTITY field for primary key.

The issue is, that some of the contacts are "attorneys," and some are "brokers," etc.

For attorneys, there are about 5-7 additional fields that I'd like to store, and for brokers there are some other fields. There are others as well, of course.

So, I'm faced with the choice, of:

1) Add 5-7 fields to the contacts table, even though these fields will only be populated for attorney type contacts. Same for other contact types...

2) Have an "attorney" table, which contains the attorney specific fields, and containing a key reference to the contacts table. Each entry in the contacts table may or may not have 1 record in this table.

Honestly, I see benefit both ways. Splitting it into separate tables seems more "right" to me, but still, I have a very uneasy feeling about it, that there must be a better way.

I would greatly appreciate anyone's thoughts on this matter.

Thanks,
Travis

 

[SemperFiDownUnda]

Split them in two this is quite common. Your base table has the common information then you have 1 child table (that happens to have its PK as a FK to your base table) for every group of additional columns that do not occur for all items in your base table.

Might seem like it is wasteful and all but its benifits outway the drawbacks in most cases.

 

[TravisLaborde]

OK, thanks! It is good to hear someone else's ideas on the topic.

Travis