Relationships/Normalization

Hello, I am having a bit of trouble understanding how I should set up some relationships.

So far I will have 3 Tables Mfr, Model, Procedures. (more to come, but just getting started)

The rules are a Mfr can not be duplicated, but can have many models.

Model numbers can be duplicated BUT a mfr, model combination can not. i.e. model 123 can be made by company ABC and XYZ, but an ABC 123 can only occur once.

A procedure can cover many models, but a model can have only one procedure.

So far I have.

MFR_Table
MFR_ID MFR_Name Website Phone etc.

MDL_Table
MDL_ID MDL_Num Description Price Image etc.

PROC_Table
PROC_ID PROC_Num (the document, or a path to the document) date, approval, revision, etc

So I guess I set a FK_Relationship between MFR and MDL, but I'm not sure how to handle the relationship between MDL and PROC applying the rules given above.

Am I even on the right track or does this require a different approach?

Thanks for any suggestions.

It seems like MDL_ID is some identity column?

We don't like those kinds of meaningless keys - the MDL_Table should have a compound primary key of MFR_ID+MDL_Num.

You already said that, basically, the MODEL is a child to the MFR. MDL_Num by itself can be duplicated - but in relationship to the MFR it is unique.

In my mind that translates to a primary key for the MODEL table of MFR_ID+MDL_Num.

There is no need for an identity type value PK for that table.

As for the PROCEDURE - you've got to give me more of a real-world feeling for what that is. If a PROCEDURE covers several MFR_ID+MDL_Num entities then a bridge table joining that would be required.

But I so infrequently use bridge tables that I find that a hard fact to believe.

What exactly is a procedure in the context of the data you are trying to model??

I am not familiar with compound primary keys. I'll most certainly look into that.

A procedure is a document, it can be .pdf, .doc, whatever. It is instructions on how to perform something.

I have attached two procedures. First is Tektronix 2245A, it covers only one model. Keep in mind that there may be many possible procedures for a 2245A out there, but this is the one that has been reviewed and approved by QA for use.

Secondly there is a procedure that covers TDS 210, TDS 220, and TDS 224. For illustrative purposes, let us assume that this procedure has been approved for use for TDS 220 and TDS 224, but not TDS 210. And that there is a separate procedure for TDS 210.

sorry 2245 exceeded 500kb. Substitute 2242.

Well - it sound like a procedure stands on it's own - simply exists.

So PROC_ID is a fine field for that PK. A single row in this table for each procedure.

Then a second table - a bridge table of sorts - would be built to relate a procedure to a mfr+model

Proc_Bridge_Tbl

Proc_Id
MFR_ID
MDL_Num

These three fields make up one large compound primary key.

If I'm understanding you right then Szlamany's bridging table will allow you to represent the fact that there are many possible procedures for each model and a procedure could apply to more than one model. But you also need a way of representing the fact that each model has 1 QA'd procedure.

You could either do this by putting a boolean 'QAd' flag on the bridging table but I wouldn't recommend this. Strictly speaking it breaks third normal form and you'd need extra code to make sure there's only one 'true' value per model.

Better would be to put a foreign key field (Proc_ID) on the model table to the procedure table. Szlamany's bridging table will represent all the possible procedures for the model, the field on the model table will represent the QA'd procedure.

Thank you both for your help. I don't know why I'm not able to visualize this.

So I picked up a couple of books, Mastering SQL Server 2005 Express Edition and SQL Demystified. Both address design and normalization, as well as a number of other topics.

Not to worry, it shouldn't be long before I'm back with more questions with obvious answers.