Efficiently storing data

[EricTheRed]

Hey. I'm looking for the most efficient way to store the following data. Whatever the solution is, speed is the primary concern as the records will be accessed very frequently.

Alright, each contact in my database belongs to zero or more categories, where the catergories are sorted into four groups. For example, one group of categories may have the following:

• media
• corporate
• politician
• government
• NPO

and the contact may belong to both NPO and politician (or neither, or...). Neither the number of items in the group, nor the names of the items are constant (they are editable by the client). I need to be able to easily retrieve, for any given category group: which categories a contact is part of, and conversely which contacts are part of any given category.

How should I store this data? I've already come up with a tentative structure for storing the category data:

tblCategoryGroups
GroupID [autonumber, PK]
strGroupName

tblCategories
CategoryID [autonumber, PK]
GroupID [foreign key to tblCategoryGroups]
strCategoryName

But I'm completely at a loss for how to say which categories a contact belongs to. If it's any help, my plan is to have the user edit which categories (in a single group) a user is part of using a multi-select list box.Oh, and for reference, my contacts are all uniquely identified by a ContactID.

Help! (The deadline is creeping up faster than I care to contemplate!)

PS - If any of this didn't make sense, just ask for clarification. I'm more than willing to do anything I can to help you help me!

 

[Sharky II]

i'm probably not in the position to try and help anyone else seeing as i'm dire at this, but seeing as some people have really helped me here so...

isn't this a many-to-many DB? a contact can have many catagories and catagory can have many contacts?

so you need to split the tables.

check out the attatched many to many DB example that was taken off this site - many thanks to teh creator

study that mate

although, i could be way off the point here

 

[SAK]

May not be the most "normalized" approach, but could you make a field for each category in each record name, doing away with the first table? The fields could be a Y/N field and check accordingly, thus allow none, some or all to be queried as necessary. If you don't have too many categories that wouldn't be too messy would it?

 

[dgm]

tblCategoryGroups
GroupID [autonumber, PK]
strGroupName

tblCategories
CategoryID [autonumber, PK]
GroupID [foreign key to tblCategoryGroups]
strCategoryName

tblContacts
contactID
contractName

tblContactLinks
linkID [autonumer, PK]
ContactID
CategoryID


Something like that maybe?

Not sure how u would go about using a multi-select listbox though...

 

[SAK]

I have a question, what is the PK arguement or function that follows the autonumber when you are setting up your ID?

 

[namliam]

PK = Primary Key

Regards

 

[EricTheRed]

Thank you for the help - the many-to-many example was especially useful. I think I have everything sorted out now, which is a Good Thing.

For anyone who's interested, I used the basic many-to-many structure, with some extra controls tacked on to group my categories (as shown in my original tables). Thanks again.