How handle these relationships?

[mscertified]

I have a structure (table) called a RULE. A Rule consists of a unique
combination of Title, Chapter, Article, Topic and SubTopic.
I also have ISSUES. Issues can be related to an individual rule or also to
any level of the rule. i.e. an issue can be related to an article or chapter.
What is the best way to do this? I will need to be able to find all Issues
for a Rule and all rules for an Issue.
What I have now is a Rule table and an Issue table and an IssueRuleXref table.
The IssueRuleXref table consists of Title, Chapter, Article, Topic and
SubTopic.
If the issue is related to a Chapter, only the Title and Chapter are filled
in. If the issue is related to an Article, only the Title, Chapter and
Article are filled in. This works but some of the queries to find Issues
related to a rule are very cumbersome.

 

[TomHinkle]

In general, it's not a good idea to make multi-field keys. you should make
each table key on one field (probably an autonumber).

Put indexes on combinations you need to be unique. You may have more
programming logic on forms to limit what they see but your joins will be
simple..

 

[Tim Ferguson]

In general, it's not a good idea to make multi-field keys.

Rubbish -- there is nothing in R theory that makes this a "bad idea".

Put indexes on combinations you need to be unique. You may have more
programming logic on forms to limit what they see but your joins will
be simple..

Again not true: there are plenty of occasions when the use of compound PKs
in heirarchies simplifies the joins enormously.

B Wishes


Tim F

 

[Tim Ferguson]

RULE. A Rule consists of a unique
combination of Title, Chapter, Article, Topic and SubTopic.

OK, that is clear; but wait a minute:

ISSUES. Issues can be related to an individual rule or
also to any level of the rule. i.e. an issue can be related to an
article or chapter.

This means that you really have to have an entity something like this:

ThingsWithIssues(*ThingID, TypeOfThing, Parent+, Description)

Notes:

ThingID is a unique identifier, e.g. an autonumber

TypeOfThing is "Title", "Chapter", "Article", "Topic", "Subtopic",
"Rule" or some form of coding or abbreviation

Parent is a foreign key that references ThingsWithIssues (i.e. a
self-join). This is (presumably) Null for all Title types but
non-null for all other types.

Description is a chance to display some human-readable identifier,
since autonumbers are pretty much useless to mere people.

If you need more fields for leaf-level Rules, then you might need another
entity

What I have now is a Rule table and an Issue table and an
IssueRuleXref table.

Actually, I'd call it AppliesTo because I prefer English names... It
would look something like this:

Issues(*IssueNumber, AppliesTo+, Colour, Length, etc)

The AppliesTo foreign key references ThingsWithIssues. An FK can only
reference one entity, so you have to put different types of things in one
table and then manage the specifics separately.


This is the slightly lazy way round. If you need to manage Chapters and
Titles etc in different ways, you may need to adopt a full-blown sub-
typing model, but I guess that your optimum solution will be somewhere in
the middle. Post back again if you need more details.


Best wishes


Tim F