Need help with creating a simple(?) ERD

[Rich S]

Howdy, all:

In nearly all of the FM tomes and tutorial DVDs I have, the authors recommend reducing table relationships in a solution down to only one-to-many (or many-to-one if you're a pessimist) relationships. I can "grok" that, but I'm having a devil of a time trying to reduce a new solution from what seems like only many-to-many relationships.

Background: I write engineering orders ("EOs") for the aircraft in our airline's fleet; they instruct the mechanics how to modify or repair an aircraft in accordance with the issuance of AD (Airworthy Directive) notices, service bulletins, and other documentation. Because the "owners manuals" of the aircraft are revised annually, supplements need to be added to some or all of those manuals until the following year's revisions are issued by the manufacturer.

What I'm trying to do is create a solution that tracks which aircraft have had--or need to have--an engineering order written for it/them as well as determine which manuals need to be revised to accommodate the change(s).

So, I have four entities (or tables):

Document - EO - Aircraft - Manuals

Here's the problem: a document (e.g., service bulletin) can affect any number of aircraft in the fleet; an aircraft can have many EOs apply to it; an EO can have more than one service bulletin to accomplish, and an aircraft has many manuals that may need to be revised as a result of the Service Bulletin or EO.

If I weren't bald already I'd be pulling my hair out on this one. As always TIA for your help!

[Fenton]

The way you describe it, it sounds like EO is kind of a parent of Document, since you could have 2 Documents attached to an EO. But you didn't answer the reverse, whether a Document can have 2 EO's attached to it? EO as a parent of Document would be odd, because the Document comes first.

So my question would be, why does an EO have to accomplish 2 documents? It would be easier if you just wrote another EO for the 2nd document. Or is it because 2 documents are basically saying the same thing, and you don't want to tell your guys to do the same thing twice?

If that is the case, and it is not real common, I think I'd just use a multi-line field in EO for that 2nd (3rd) Document. Kind of "low rent", but might take care of it. Is one EO always for one particular Aircraft?

I'm kind of rambling here, and I could be wrong. But I think we need to know where many-to-many vs. one-to-many might be needed.

[Fitch]

Do Service Bulletins and EOs always pertain to Manuals? (Or can they apply to an Aircraft independent of a Manual?)

Can a Manual apply to more than one Aircraft?

Assuming the first answer is yes and the second is no, I picture something like:

Aircraft -< Manuals -< Revisions >- SB/EO

IOW:

Aircraft to Manuals is one to many

Manuals to SB is many to many

Manuals to EO is many to many

Revisions is the join table we use to normalize the many to many relationships. You might need two join tables, but you could probably go "low rent" with a couple of key fields in one table, or use Fenton's "low rent" method.

[Rich S]

Great questions! I apologize for not being very clear in the first place so I'll elaborate on the parsing a little bit...and yes, Document is the first link in the chain, so let's use a Service Bulletin ("SB") as a field choice in Document.

So, looking from...

Document to EO: one-to-one since one EO will be written per one SB;

Document to Aircraft: one-to-many since multiple aircraft may be affected by an SB;

Document to Manual: one-to-many since more than one manual may need to be revised because of the SB.

Looking from...

EO to Document: one-to-many since more than one source document may be used in the EO: an SB, AD Notice, TIN, etc.;

EO to Aircraft: one-to-many since an EO can affect more than one aircraft;

EO to Manual: one-to-many since multiple manuals may need to be updated/revised by the contents of the EO.

Looking from...

Aircraft to Document: one-to-many since more than one (source) document may affect an aircraft;

Aircraft to EO: many-to-many since an aircraft has more than one EO assigned to it and an EO can affect more than one aircraft;

Aircraft to Manual: many-to-one since many aircraft are addressed in a specific manual;

Looking from...

Manual to Document: many-to-one since the (source) document and affect many manuals;

Manual to EO: many-to-one since an EO may affect many manuals;

Manual to Aircraft: one-to-many since a manual contains data addressing many aircraft.

Fitch--it's a "yes-yes" to your two questions.

Thanks again!

Rich