Please help with my table layout for donors. A donor can be an individual or an organization. If the donor is an individual, the donor record needs to include standard name and address attributes (home and work), and spouse name. If the donor is an organization, the donor record should include organization name, primary contact name and address attributes, and alternate contact name and address attributes. I have the possibility of 2 contact types for donor organization and 2 address types for donor individual.
I created a table for donors with the shared attributes of donor_id (PK), donor_division_code (FK), donor_status_code (FK), and donor_notes. These are the only attributes common to the subtypes, organizations and individuals. Donor_notes is the only non-key field. Organizations and individuals are subset tables of donors and will have the same PK as donors. Is it reasonable to have a supertype, donors, with only one non-key field?
The subset table, organizations, has a one to many relationship with contacts since there can be multiple contacts for a single organization. Can a subset table be related this way? The subset table for individuals does not have a relationship to contacts.