Searching for an elegant solution

[WillYum]

Greetings!

Here's what I'm wanting to do.

Have
tblContacts
- ContactID (Primary Key)
tblCategories
- CatID (Primary Key)
tblSubCategories
- SubCatID (Primary Key)
tblCategoryLink
- ContactID
- CatID
- SubCatID

tblCategoryLink serves as the M:M between tblContacts & tblCategories.

The trouble I'm having is the most elegant solution to keep track of the option sub-category for each category. Each sub-category is linked to a specific category.

Now currently in tblCategoryLink I have:
ContactID
CategoryID
SubCategoryID

Is this the most elegant solution?

It doesn't feel like it for some reason. Mainly because I can't require (in the tables) that the subcategory be a child of the category. In forms I can accomplish this and do a check but that seems like a copout?

I considered having only SubCategoryID and ContactID in the tblCategoryLink but I'm not sure if this would limit the ability to list Contacts by CategoryID and/or SubcategoryID (which are both options I need).

I also considered another M:M to keep track of the SubCategories seperately but that seemed even less elegant.

(Note: I'm not saying is has to be elegant but elegant is usually associated with at least a superficial simplicity which makes it easier to explain to people who are not familiar with the system, i.e. It is simpler to say Contacts can belong to many categories and/or subcategories than to describe many to many tables).

Any help will be appreciated.

 

[lespaul]

I also considered another M:M to keep track of the SubCategories seperately but that seemed even less elegant.

This would be the solution that followed database normalization rules.

Have you read The Fundamentals of Relational Database Design?

Leslie

 

[willir]

Hello WillYum

I was fine with your solution for category and contacts. A contact can be grouped into more than one category. A category can have more than one contact. ...Perfect.

I got lost on the subcategory. I know that a subcategory can refer to a refinement of the category.

Let's talk computers for a sec.

I can have
- workstations
- laptops
- servers
- switches
- printer
...etc

But, for printers...
- laser
- dot matrix
- solid ink
AND
- system printer
- local printer
- application printer

Or, with workstations
- end user workstations
- shared workstations
- production workstations
- multi media workstations
etc.

IF this is what you are refering to as subcategories, then using a category -> subcategory is indeed awkward.

Notice with the printer example, not only did I have categorize the printer by type - laser, inkjet, etc, but I also categorized it by how the printer was accessed - local, networked, application.

There are various solutions. Here is one which may or may not work for you. It does involve a bit of coding. Use a type of "code" for the category...

PrtNetLaz - printer, networked, laser
PrtLocInk - printer, local, inkjet
WstUsr - workstation, end user
WstPrd - workstation, production
LapUsr - laptop, end user

The M:M is still set between the grouping category and the entitiy (devices in my example, contacts in your problem).

The category table is setup a bit differently...

tblCatogory
CatCode - pk
CatGroup
Description

Sample data using my device example...
[tt]
tblCatogory
CatCode CatGroup Description
PrtNetLaz Printer Network laser printers
PrtLocInk Printer Local inkjet printers
WstUsr WorkStation End user workstation
WstPrd Workstation Production workstation
[/tt]

By referencing the CatCode, I know what type of category group the category code belongs to -- prinetr, workstation. I can also use the CatCode to search for more than one property. Find all local printers, find all user devices, find all lazers.

So, if I have a cascading combo box, I can have the first one offer printer, workstations, etc. And the second combo box would offer what type of printer, what type of workstation.

The "work" involved is in how you design the category table, and if any hard coding is required.

In the above example, I purposely used a 9 letter code for printers, and a 6 letter code for workstations. I would have to accommodate for this difference in the SQL statements. ...OR... I could use a 9 letter code for all devices.

However, I still have a M:M between category and the main entity. The category table accommodates the group / subgroup issue.

It works, but I am not sure if this is the solution you are looking for.

Richard

 

[WillYum]

Richard,

Other work prevented me from responding sooner. However, I get what you are saying and in fact it relates to my thought that I should directly link what I called the "SubCategory" table to the "MainContacts" table. Then have a link between that and the main category grouping. It's exactly what you propose but with different names.

I might change the names to make the logical relationship a little clearer. Maybe link Main Contacts to Category table (M:M) and then link the Category table to a GeneralCategory or SuperCategory table.

Your example clarified it for me and though it will take some coding I think it makes the most sense in the long run. Thanks!

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?