Database design recommendation: contacts database

[wes_solar]

I'm designing a new database and I'm hoping for some advice or recommended reading...basically just where you think I should go from here.

The database is for holding contact information. The contacts are of various sorts for our business: customers, friends, colleagues, contributors, etc. We want to use the database for searching for particular contacts and their information or pulling together lists for sending out e-mails and snail-mail. For example, we might want to pull a list of all of the contributors to send them an email about a new project.

Do you know of any good example databases I could look at for ideas? Or a good website to learn standard practices?

Thank you,
Wes

[griswolf]

I learned most of this stuff by asking questions of an expert, so don't have much experience with tutorials online. Google gives some help, of course:

• This one starts very far back (flat file storage), but seems to cover the bases pretty well: http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/intro.html
• http://www.databaseanswers.org/tutorial4_db_schema/index.htm (slide show and or wmv file)
• What you are really looking for is a 'normal form' schema for your database, so: http://databases.about.com/od/specificproducts/a/normalization.htm

And finally: I'd do it approximately this way:

• A table holding person contact info, with an id primary key
• Some tables that hold various things that might be associated with people, with an id primary key (maybe blog entries, or outbound email contacts)
• Some association tables that match a person with item(s) in those tables: basically a pair of foreign keys: person_id and thing_id, with date info. (person 3 posted blog 97 May 12th at noon; person 9 was sent our email 111 May 30th at half past 9)

One of the things you need to associate with people is their contact preferences, and it isn't quite as simple as it first appears: I work with a system where people log in to register for an event, and in my system, people have default contact preferences that can be overridden in the registration process, so the person_event relationship has to track that possible difference from the default.