Membership Database - Companies v. Individuals

[LenaOlson]

I am new to databases. I have taken a couple online video courses on database structure and design on Lynda.com. Everything says planning up front is most important, so I'm trying to think of what I want to accomplish with a database.

Lost even before I start. I want to track Memberships. So far, I have an address, a contact table (people) and company table. I get lost when I realize that a member can be either a person, a couple, a farm or ranch or a company. How do I get my membership info from the different tables. I hope someone can get me started in the right direction.

Thank you.:banghead:

 

[Frothingslosh]

Don't worry about the tables right now.

Step one is to figure out in general terms what you want to accomplish. In a business scenario, this could be as simple as "We want to keep track of our inventory" (please note that that really is NOT SIMPLE) or as complicated as "We need a a project tracking system to keep track of hours used and hours billed against project forecasts, complete with tracking. It must also be tied into our HR software as well as being able to track purchasing, shipping, receiving, and inventory status as our widgets and geegaws are assembled and shipped out."

Once you do that, try to figure out what you need to track and why, such as members, plans, employees, locations, etc. In a business scenario, this could go quickly, but in my experience it more often than not winds up with you in lots of long meetings listening to management and C-levels argue with each other.

Then try to figure out all the data you need to track for each of the items you listed. Members may need address, join date, active/inactive status, etc.

Only once you've done that should you start worrying about tables and the like.

 

[LenaOlson]

Thank you for your response. I keep trying to do this, but get totally hung up when I know that my member # can either be attached to a person or a company. I want to keep my people and companies separate because they will both be a many-to-many relationship. In the end I want a report that has Member #, Member name, Type of Member, Date Last Paid. Currently I have a spreadsheet for the Members, a spreadsheet for the Affiliates, a Spreadsheet for our sponsors. But, one person or company can be on all of these lists and there are too many spreadsheets to keep address/contact information current.

If I can get past this one question/issue, I think I can get going better.

Thanks again.

 

[Frothingslosh]

Don't. Members are members. If you absolutely need to differentiate, put in a field to do so, such as an "institutional" check box or a linked table with "membership type'.

I wouldn't call it the first rule of Access, but one of the big ones is that data (in this case, member type) is always stored in a field, never in the structure itself.

I think the very first thing you should do is read up on 'normalization'. It's a moderately complex topic, but in a nutshell, it means both that each table should generally be about one 'thing' - employees, members, sponsors, locations, etc - but that there should also ONLY be one table for that thing. Just as you don't make a table for US employees and a different one for Candian employees, you wouldn't make one table for individual members and a different one for corporate members. You make one Members table with a field that differentiates between the types.

Following the rules of normalization will make your life a LOT easier as the project gets going.

 

[jdraw]

I agree with frothingslosh -- write a 5-6 line description of what you are trying to do. Start with simple English and a view from 30000 ft. Revise as necessary to add detail to get a clear description of the "business involved".

Here are a few examples of the sort of detail required (all from Rogers Access Library).

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

Another
Roger Carlson's CIS 253 class wants to create a database to store information about the students in the class. Information will include student demographic information, contact information and course information and history.

Information about the students will include name, address, city, state, zip, phone, email, fax, college major, Social Security Number, and gender. Each student can have more than one phone number, email address or fax number.

Course Information includes course name, course number, number of credits, and grade received. Each student will take many courses in their college career. And naturally, each course will have many students enrolled.

and another

Problem Narrative:
CaterIt Database


Sue Johnson is starting a catering business. She is looking for a program to help her maintain her business. She wants to start by tracking customers and orders. She will eventually want to add accounting features like accounts payable, accounts receivable, and inventory control, but wants to start small.

Customers are the people to whom she sells her catering services. Customer information includes: Name, Address, Phone, Alternate Phone, Fax, and Email. A customer may place many orders.

An Order is a group of items delivered as a single unit to a single Customer. An Order contains the customer information, the order date, delivery instructions and an itemized list of the Items delivered. An order applies to only one customer at a time, but each order can have many items on it.

Items are the individual items that appear on an order. Each item has an associated item number, item description, quantity, price and extended price. Each item can appear on many orders, and can appear on one or more orders.

There are tutorials for each of these scenarios at
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

A clear statement of the problem/opportunity helps keep focus. Also, you may want to describe/define each of the "things"/"entities"/eventual tables in a line or two.

Good luck.

 

[LenaOlson]

Thank you all. Okay, is Rogers Access Library a good starting point? Is there one great book out there that will get me there? I though Lynda.com would help, but it isn't too much.

If I write out a 3-4 paragraph of my Association and the think I want to accomplish, can I post it here at get critique/suggestions from you guys? I appreciate very much your help.

Martha

 

[jdraw]

The tutorials at Rogers Access Library are excellent for concepts and all tutorials have solutions. The tutorials basically take you from a business description to an entity relationship model -- which is really a blueprint for your database.

Try 1 or 2 and experience designing a database. The tables and relationships are critical to a useful database application.

Good luck , and let us know what you think after having worked through a tutorial or two.
And yes, you can post your description(s) and you will get some criticism/comments.

 

[LenaOlson]

Thank you all. Here is my first description attempt. I am going right to Rogers Access Library after this!! I sure appreciate all you help.

This is a small, non-profit organization of cattlemen. We raise funds by donations, sponsorships and memberships. We have one main event/conference per year where we give educational opportunities all to benefit cattlemen.

Individual or companies can be a member of our organization, a member of our parent organization, an affiliate (meaning support and a possible seat on the board), or a sponsor. The individual or company can be one or all of these. There are different types of memberships for our organization and for our parent organization. Individuals can belong to several of the companies/organizations and each company/organization will of course have many people.

I want to track our members, affiliates, sponsors, etc. and know when they first became a member, when did they renew, when does the membership expire (so reminder can be sent). I also want to track what type of farm/cattle they have (one or all of 5 options). How much did they pay, check number, etc. I also want to attach a copy of the payment to the record.

I will also have contacts, etc. that I will just want to maintain who are not necessarily members. Obviously, I need phone/address/email/website information for all. At conference time I want to track registrations and payment information.

This doesn’t seem like it should be that difficult. My main objective is to have address/phone/email information in one place instead of having to change an address in many places.

Another though, the affiliate organizations will have a person who will represent that organization on the board of directors.

I get that I need members, affiliates, sponsors, because they are all one thing, but still get lost with the affiliate, sponsor or member being either a person or a company.

I hope this might help and maybe you can give me some suggestions. In the meantime, I will read up more on normalization. Thank you all.!!!

 

[jdraw]

A very good start.
For many readers, some of your terms may need descriptions such that recognizing or distinguishing a sponsor from an affiliate is clear. I would recommend a 1 liner description for each of your terms --it will help readers, but it will also help you.
There will be some attribute feature of each of these that is clear to you, but not to the reader ( donations, sponsorships and memberships).
The tutorials will help you with Normalization ( a fancy term to put relevant data into the proper place so you don't have to change address in several locations.)

Try a tutorial; experience his technique/approach;then try it with your own opportunity/issue.

Good luck.

 

[LenaOlson]

I did by the book "Database Design for Mere Mortals"
and am reading it. I have also downloaded a couple of the scenarios from Rogers. I have done another attempt with a table list and fields. Appreciate any comments.

Martha administers a nonprofit membership organization. Things she needs:

v Track address, phone, email, etc. information for INDIVIDUALS who are in some way connected to our industry.
v Track address, phone, email, etc. information for COMPANIES who are in some way connected to our industry.
v Track BOARD OF DIRECTORS of organization.
v Track affiliate members (An Affiliate Member supports the organization financially and has the option of having someone from their organization on the Board of Directors) of the organization who are normally organization/companies with a specific contact person and board member (which are not necessarily the same person).
v Track organizations/people who sponsor (Sponsors contribute financially, recognized on web, newsletter, etc.) our organization with funds.
v Track Members of the organization and the type of membership (the members may be either individuals or companies).
v Track the details of all payments into the organization, sponsorships, memberships, event attendance, etc.
v Dates of all payments, expiration date of memberships for report and sending out reminders.
v Track Invoices and Payments sent to accountant

Preliminary Table List
BOARD OF DIRECTORS
MEMBERS
AFFILIATE MEMBERS
SPONSORS
PAYMENTS
INVOICES
VENDORS
COMMITTEES
CONTACTS
BUSINESS/ENTITY
ADDRESSES

CONTACTS
BUSINESS/ENTITY
COMMITTEES
ContactID – PK

BusinessID-PK

CommitteeID PK

LastName
BusinessName
Name
FirstName
Phone-Work
ContactID FK
Phone-Home
Phone-Fax

Phone-Cell
Website

Email
AddressID FK

AddressID FK

ADDRESSES


AddressesID PK



Address1


Address2


City


State


Zip


County



VENDORS
INVOICES/EXPENSES
EXPENSETYPE
VendorID-PK

InvoiceID PK

ExpenseTypeID PK

BusinessName
ExpenseTypeID FK
ExpenseType
Phone-Work
VendorID FK

Phone-Fax
Description

Website
Amount
SPONSORDESIGNATION
AddressID FK
Date
SponsorDesignationID-PK


DatetoAccountant
SponsorDesignation







SPONSORLEVEL
INCOMETYPE

SponsorLevelID PK

IncomeTypeID PK
INCOME
SponsorLevel
Type
IncomeID PK

AmountRange

ContactID-FK


BusinessID-FK


IncomeType FK
SPONSORS
AFFILIATES
Description
SponsorID PK

AffiliateID PK

Amount
ContactID-FK
BusinessID FK
Date
BusinessID-FK
AffiliateBoardPosition (Y/N)
CheckNo.
SponsorLevel-FK
ContactID FK
DatetoAccountant
SponsorDesignation-FK
Repr – ContactID FK






MEMBERS
MEMBERTYPE
BOARDDIRECTORS
MemberID PK

MemberTypeID PK

BoardMemberID

MemberType FK
TypeMember
ContactID
ContactID

BusinessID
BusinessID

BoardMemberTypeID FK
WCANumber

StartDate
NCBANumber
BOARDMEMBERTYPE
EndDate
PymtID FK
BoardMemberTypeID


PymtDate
TypeBdMember

ExpirationDate

 

[jdraw]

Looking good. A few observations:

Use only alphanumeric and underscore "_" characters for field and object names. Embedded spaces are/can be a syntax nightmare. Do not use "-" (hyphen/minus sign) with Access, it will require special/extra syntax.

Did you work through a tutorial? Any comments?

Some additional thoughts Definitions required for:
INDIVIDUALS
COMPANIES
our industry
in some way connected to our industry
BOARD OF DIRECTORS
organization
Members
type of membership (the members may be either individuals or companies).
Payments
Invoices
Reminders
sponsorships, memberships, event attendance etc (etc is a red flag ---lacks precision and definition)

Definitions would clarify the next 2 lines which seem to have financial support in common
--An Affiliate Member supports the organization financially
--Sponsors contribute financially


There is a free data model related to members here, but it may not apply to your situation. However, it may help you with some tables and relationships generally.

 

[Cronk]

Lena

Another thing to consider is that an individual can presumably have multiple roles (member/affiliate, sponsor, board member). You don't want to be maintaining people data in multiple tables.

That is use one people table with all contact and whatever personal info you need stored in this table, and use IDs in other tables to indicate which role(s) a person has.