Database design : Mutliple and growing categories

[RichardatManAlive]

This appears simple enough but I'm going round in circles with it.
I have a growing contacts database with the usual contact data , being used
to send mail merges via email and mail.
I need to categorise each contact record several ways
So contact 1 may belong to category 1 , contact 2 could belong to category 1
and category 2 , and so on.
The number of categories is intially fixed at 5 but will grow as the
database grows and the query needs become more complex.

I need to develope a structure that will allow easy creation of queries ,
easy updating of data entry forms as the categories grow and change.

My intial thoughts were to simply set up a series of fields category 1....5
etc for each cleint record. But them I may need to split one category into
two ( eg Lawyers into Lawyers and Solicitors ) thus I'll need a simple
maintanence system to manage these changes.

Richard

 

[johnnymac43]

Hi Richard,

You are right to seek help at this stage as it will be so much harder to change your design later on.

For this (almost classic) many to many relationship between contacts and categories, you need three tables. These will be for contacts, categories and links. Use an autonumber field for the id in each of contacts and categories, regardless of any other identifiers (unique or otherwise) that you may have. Each record in the link table requires only 2 numeric fields, for contact id and category id.

Once you have this structure in place you will need to build a data entry form that allows you to enter and maintain your data. If you need help with this part of the process it will be willingly given by many programming experts on this forum, and they will praise you for your data structure rather than suggesting that you change it.

Cheers

John

 

[RichardatManAlive]

Thanks John,
I agree this little structure is very elegant.

I just tryed a mock up of the tables and am now realising what you alluded to at the end of your post, the data entry and management form will be the challenge! Seems I'll need combo boxes or option groups for the user to select categories for each client, then some code to append the appropriate number of records to the linking table. This may be a challenge as I have only just started with VBA , but its good really, I need to wean myself off macros.

Richard

 

[JeremyNYC]

John's spot on. For some reading, check out Paul Litwin's "Fundamentals of Relational Database Design". There's a copy on my website.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Professional Development for Clients Large and Small

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[RichardatManAlive]

Thanks Jeremy,
I downloaded your copy of Paul Litwin's "Fundamentals of Relational Database Design". Good stuff, some I knew already but I know have a more robust explanation, so thanks.

I issue I am now facing is a normalise database design can lead to more work creating the forms and queries needed to
manage the data. But I'd rather the challenge of deisgining new forms etc than that of patching my way round bad data design

Richard

 

[JeremyNYC]

Richard,

_Exactly_.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[willir]

RichardatManAlive

johnnymac43 and JeremyNYC have pointed you in the right direction regarding many-to-many (M:M) relationships.

I developed a similar contact database for IT support. In my case, I had a M:M for supported devices -- one tech supports many devices, and many techs support one device; and technical skills -- one tech has many skills, many techs have this skill. When it came to displaying the information, I found a very simple continuous subform embedded in the contact form worked best.

In your case, contact 1 belongs to categories a, b, c...
Then have a category table that displays the contacts for belonging to the categories.

A list box depicting each relavent category may also work.

Richard