Looking for experienced view on my Design.

[Dana809904]

Hello,
I am developing a Contact Database which keeps track of Individuals AND
Organizations. An important issue is that Individuals may, but not always,
belong to an Organization. I will give detailed Individual and Organization
info as well as general info on the other tables in the database.

Individual Table:
IndID_PK, OrgID_FK, IdentificationNum, Prefix, LastName, FirstName, Title,
Nickname, Referrer, Priority, Address, City, State, Zip, Workphone, WorkEXT,
Homephone, Mobilephone, Fax, Email, Web, RecordSource_FK, Comments

Organization Table:
OrgID_PK, OrganizationName, DepartmentName, SICCode, Referrer, Priority,
Address, City, State, Zip, Workphone, WorkEXT, Homephone, Mobilephone, Fax,
Email, Web, Comments

There is also a table Seminar, which contains a listing of Seminars coming
up; SeminarSchedule, which links Seminars with Individuals that will attend;
Source, which lists the source Excel file where the Individual information
came from; and Mailing, which links Individuals and Organizations with
mailings that have gone out for a particular Seminar.

I am having issues with getting certain things running smoothly,
particularly the mailing of Individuals and Organizations together. Here is
the mailing table structure:

Mailing:
MailingID_PK, SeminarID_FK, IndID_FK, OrgID_FK, RecordSource_FK, DateSent,
Comments

I am thinking that it could possibly be better to somehow combine the
Individual and Organization tables into one. But there is the issue of
linking certain Individuals with an Organization.

If anybody would take the time to check this out and give me ANY comments on
their thoughts of my database design I would GREATLY appreciate it! If you
need any more info just ask and you will receive!
Thanks in advance,
Dana S.

 

[Geof Wyght]

Dana,
Here are my comments on your design.
1) If people may not always belong to an organization,
then take the OrgID_FK out of your Individuals table. Make
an Individuals - Organization cross-reference table
instead, containing only IndID_PK, OrgID_FK.
2) Since we now have many type of phone numbers you might
want a separate phone type table. Then make a separate
Individuals - Phone table with IndID_PK, phonetypeid,
phone number, extension. This way, if yet another type of
phone appears (TDMA wireless vs GSM wireless for
instance), you just have to add another phone type.
3) Postal addresses usually have 2 address lines.
4) I wonder why the Organization table has a home phone
number?
That's it! Good luck.
Geof.

 

[Dana809904]

Geof,
Thanks a lot for your insights. I will definitely take them into
consideration and act upon them appropriately. If anyone else has any
thoughts or comments I am still open for constructive criticism or whatever!

Thanks again,
Dana