Storing donor addresses in a relational database

Question

I am building a donor database for a non-profit organization and one issue I'm mulling over is how to store some of the donor data. There are some families for whom we capture the names of both spouses, hence each spouse gets its own record in the DB, and naturally they share the same address. Because it is likely to happen that multiple people from a family will exist in the database, and they will share the same address (husband, wife, kids), I am considering storing the addresses in a separate table and having Address_ID be a foreign key on the Roster table. This would reduce the amount of duplicate data, and when it came to address changes, it would be far easier to handle for multiple donors that are part of a family.

Table: Roster
Columns:
Roster_ID (PK)
PrimaryRoster_ID (FK, references Roster_ID of the head of the household)
Address_ID (FK)
FirstName
LastName
Phone

Table: Address
Columns:
Address_ID
Street
Apt
City
State
ZipCode

I haven't build the GUI yet for entering the data - I'm still building the DB and populating it via scripts, but the idea is that when a new donor is created, he/she is assigned the Primary, and then any other donor who is part of the same family will have the primary donor's Roster_ID populated in the PrimaryRoster_ID column so that a link can be made between family members.

People in the same family don't have to have the same address (if they don't, a separate Address record would be made for that donor), but if there are 4 people in a family, all with separate DB records and the user who enters the data assigns the same address record to all of them, only one record will exist in the Address table, with 4 records in the Roster table having that Address record as an FK to associate each individual to that address.

Can anyone think of a better way of handling this, or would this design be sufficient? The size of the database right now is around 150 records in the Roster table, with maybe a max of 1000 records down the road.

Edit: Changed table design slightly - I realized that Address_ID should really just be an FK in Roster instead of having a Roster_Address table.

Answer 1

With at most a few thousand records, this (part of the) database is not particularly large, so there is no particular need to split off the address information to conserve space.

With space considerations out of the way, the most important reasons that influence how to store the address information are the business rules. For example, if the organisation does not do anything with the fact that two people live at the same address, and there is no indication that this might change in the foreseeable future, then the fact that two table rows contain the same address information is purely incidental and there is no need to create a separate address table. Just like you wouldn't think of creating a separate table of first and last names just because there might be multiple persons called "John Smith".
The same goes for the relation among family members. If that is of no concern to the business, then why bother putting it in the database.

On the other hand, if the organisation distributes a magazine among its members, and they have the policy of sending only one copy per family living at one address, then your design is certainly appropriate and sufficient to meet the requirement of the business rule.

---

Regarding the design of the address table itself, you have to ask yourself if all relevant addresses can be represented in that format, or if you might need a different table layout. Especially of the organisation might have to deal with foreign donors, you might need to opt for a much more free format for the addresses, because nothing can vary as widely between countries as how they represent street addresses.