Hi,

I'm having a bit of trouble with the design of a database and would appreciate it a lot to get some feedback from people more experienced in the field than myself.

I'm trying to design the database for a fictional charity that has its fingers in many pies- it sells items in a shop to individual customers, buys and sells from other companies, accepts donations and provides care for individuals needing support as well. While I'm not to worry about the sales side of things (referencing products etc), the "charity" does need a database storing information on its contacts that have consented to be on it.

The big problem is that everything can overlap with everything else in some way. An organization linked to the charity can be a buyer, a supplier and/or linked to the charity through contributions. Individual contacts may be customers and/or be linked to an organization and/or related to someone in care of the charity etc. A small business may have a contact which uses his/her individual details as organization info as well, and may also be connected to them through individual means too. A business may also omit some information (such as a contact name) and only supply general information (eg. a phone number).

Without going into too much detail on the contact side of things, I’m having a bit of trouble wrapping my head around the organization part. At the moment, there isn’t too much difference between the information held for Suppliers, Buyers or Donors beyond that they are Suppliers, Buyers and/or Donors (but this may change in the future). However, an organization may have different addresses and different contacts because of a number of reasons- their supplier address may be different from their delivery address; organizations may have more than one branch; different people act as different contact points etc. If organizations had one solid role (e.g. “supplier”) or didn’t have links to individual contact information, it would be less problematic, but since everything can end up linked to one another through a variety of means, getting the relationships right is fundamental so that data duplication doesn’t happen and any data updates get changed across the board.

One idea was something along the lines of:
Organization(Org_PK, Org_Name) *General organization details
Type_Link(Org_FK,Type_FK) *Allows an organization to be assigned multiple roles
Type(Type_PK,Type) *Acts as an LoV for roles- “Supplier”, “Buyer” etc
Contact(Cont_PK, FName, LName, Phone, Email) *General contact details
Contacts(Cont_FK,Org_FK) *Allows contacts to be linked to organizations
Address(Add_PK,Address_line_1…, Postcode, County, Town) *Address info
Addresses_C(Add_FK,Cont_FK) *Can link one or more addresses to contacts
Addresses_O(Add_FK,Org_FK) *Can link one or more addresses to organizations

The downside is it would leave information somewhat general as you wouldn’t be able to assign contacts and addresses specifically to parts of an organization (“Supplier” etc) and only link them generally to it.

Another idea was:
Organization(Org_PK, Org_Name) *General organization details
Org_Link(Org_FK,Type_FK, Contact_FK, Add_FK) *Allows an organization to be assigned multiple roles and can assign a contact and address specifically for these roles (Eg. “Bill” works at “OrgA”. “Jill” works in the “Suppliers” section of “OrgA” and her work address is…)
Type(Type_PK,Type) *Acts as an LoV for roles- “Supplier”, “Buyer” etc
Contact(Cont_PK, FName, LName, Phone, Email) *General contact details
Address(Add_PK,Address_line_1…, Postcode, County, Town) *Address info
Addresses_C(Add_FK,Cont_FK) *Can link one or more addresses to contacts

This would allow addresses and contacts to be linked more clearly to an organization and it is the option I’m leaning towards. I’m unsure about just using Org_Link to handle a good chunk of the relations and the inevitably large amount of null values that it would end up storing though.

A third design I’ve been toying with:
Organization(Org_PK, Org_Name) *Organization info
BuyerOrg(Buy_PK, Org_FK…) *Wholesale info
Supplier(Sup_PK, Org_FK…) *Supplier info
Donor_C(Don_PK,Org_FK…) *Donation info
Contact(Cont_PK, FName, LName, Phone, Email) *Contact Info
Address(Add_PK,Address_line_1…, Postcode, County, Town) *Address info
Addresses_C(Add_FK,Cont_FK) * Can link one or more addresses to contacts
Addresses_O(Add_FK,Org_FK) *Can link one or more addresses to general organization info
Addresses_B(Add_FK,Buy_FK) *Can link one or more addresses to the buyer section of an organization.
Addresses_S(Add_FK,Sup_FK) *Can link one or more addresses to the supplier section of an organization.
Addresses_D(Add_FK,Don_FK) *Can link one or more addresses to the donation section of an organization.
Contacts_O(Cont_FK,Org_FK) *Can link one or more contacts generally to an organization
Contacts_B(Cont_FK,Buy_FK) *Can link one or more contacts to the buyer section of an organization
Contacts_S(Cont_FK,Sup_FK) *Can link one or more contacts to the supplier section of an organization
Contacts_D(Cont_FK,Don_FK) *Can link one or more contacts to the donation side of an organization

This would allow for easier expansion if more unique information about suppliers, buyers or donors came to light. However, if more roles were added (e.g. “Support Organization”) it would make expansion in that direction difficult as new tables would have to be added. The amount of relationships may make following the design of the database hard as well.

I’m sure I’m missing something, or have overlooked something, or have done something completely wrong in the designs and I would be grateful for any help or comments anyone could provide.