[FONT="]I’m creating a database for a fictional charity that runs a shop and cares for individuals. However, I’m not sure my line of thinking is right and would appreciate feedback from those more experienced than myself in such areas.[/FONT]
[FONT="]Everything can overlap to some degree- an individual linked to the charity may be linked because they are a customer, or are connected to an organisation known to the charity, or be a family member to a tenant, or be a tenant themselves etc (or a combination of any of the above). An organisation may buy from the charity, supply the charity, donate to the charity etc or all of the above. Contacts may have multiple addresses/faxes/emails/phone numbers and companies might have multiple points of contact as well.[/FONT]
[FONT="]Product information and order information is unimportant at the moment as the database is to be used more as a contacts database, holding contact information on people and organisations linked to the charity and the reason why they are linked (Are they suppliers? Are they customers? Etc).[/FONT]
[FONT="]What I’ve done is create a general human contact table and a general organisation table. From there, extra information can be assigned to them depending on their links with the charity. Contacts can be assigned multiple customer types (eg “Shop Customer”, “Mail Order”). Contacts can also be assigned data to do with tenancy. Tenants have information about where the charity houses them, as well as data to do with who their social workers and family members are. The purpose of the FamMember table and the SocialWork table is basically to define a many to many relationship which says that Contact X is Tenant Y’s Family/Social Worker. Contacts can also be linked with organisations and their role in the organisation can also be specified.[/FONT]
[FONT="]Organisations can be assigned multiple types (“Supplier”, “Trust” etc) and multiple addresses with the option of linking types to address just in case an organisation has multiple branches, or one department has a different address than another (eg Supplier information goes to address A, Donation information goes to address B).[/FONT]
[FONT="]
However, I’m somewhat sceptical about certain areas of the design- mainly dealing with phone, email and fax details and contacts/organisations. At the moment, I have contact channels linked many to many to organisations and contacts where the person inputting the data must specify whether the channel is email, phone or fax. The relationship is many to many because contacts can share the same contact details as others- for example a husband and wife known to the charity may share the same phone number and email address, as well as postal address too. Is it better to follow this setup or is it better to have tables specifically for handling phone, fax and email? Further expansion may look at using Access in conjunction with Outlook to email (groups of) people and I’m wondering if I’m shooting myself in the foot with my current approach as it will result in slight complications later in having to use slightly more complex queries to extract email information. [/FONT]
[FONT="]Any other criticism of the design is more than welcome- I know I’ve been quite lazy with keys at the moment and am also looking to better improve that.[/FONT]
[FONT="]Everything can overlap to some degree- an individual linked to the charity may be linked because they are a customer, or are connected to an organisation known to the charity, or be a family member to a tenant, or be a tenant themselves etc (or a combination of any of the above). An organisation may buy from the charity, supply the charity, donate to the charity etc or all of the above. Contacts may have multiple addresses/faxes/emails/phone numbers and companies might have multiple points of contact as well.[/FONT]
[FONT="]Product information and order information is unimportant at the moment as the database is to be used more as a contacts database, holding contact information on people and organisations linked to the charity and the reason why they are linked (Are they suppliers? Are they customers? Etc).[/FONT]
[FONT="]What I’ve done is create a general human contact table and a general organisation table. From there, extra information can be assigned to them depending on their links with the charity. Contacts can be assigned multiple customer types (eg “Shop Customer”, “Mail Order”). Contacts can also be assigned data to do with tenancy. Tenants have information about where the charity houses them, as well as data to do with who their social workers and family members are. The purpose of the FamMember table and the SocialWork table is basically to define a many to many relationship which says that Contact X is Tenant Y’s Family/Social Worker. Contacts can also be linked with organisations and their role in the organisation can also be specified.[/FONT]
[FONT="]Organisations can be assigned multiple types (“Supplier”, “Trust” etc) and multiple addresses with the option of linking types to address just in case an organisation has multiple branches, or one department has a different address than another (eg Supplier information goes to address A, Donation information goes to address B).[/FONT]
[FONT="]
However, I’m somewhat sceptical about certain areas of the design- mainly dealing with phone, email and fax details and contacts/organisations. At the moment, I have contact channels linked many to many to organisations and contacts where the person inputting the data must specify whether the channel is email, phone or fax. The relationship is many to many because contacts can share the same contact details as others- for example a husband and wife known to the charity may share the same phone number and email address, as well as postal address too. Is it better to follow this setup or is it better to have tables specifically for handling phone, fax and email? Further expansion may look at using Access in conjunction with Outlook to email (groups of) people and I’m wondering if I’m shooting myself in the foot with my current approach as it will result in slight complications later in having to use slightly more complex queries to extract email information. [/FONT]
[FONT="]Any other criticism of the design is more than welcome- I know I’ve been quite lazy with keys at the moment and am also looking to better improve that.[/FONT]
Attachments
-
CharityDB2.jpg
