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.