best way for connecting tables in the following

[ryetee]

I have a table which describes parts.
Each part can have up to 5 different types of analysis.
On the part table I'm going to put 5 fields
Analysis 1
Analysis 2
Analysis 3
Analysis 4
Analysis 5
Now do I create 1 new table with the above fields linked to it
ie
ID
ANALYSIS TYPE
ANALYSIS DESCRIPTION

SO that each of the five fields will link with the same table but Analysis 1 can only take the ANALYSIS DESCRIPTION if "ANALYSIS TYPE" = 1 etc etc
Or do I create 5 different tables with just
ID
ANALYSIS DESCRIPTION

Hope this makes sense?

Dave

 

[pr2-eugin]

I have no idea what your business requirement is, but IMVHO both method you proposed is not the way. You might need three tables, like..

tblProducts
productID (PK)
productDescription
'any other related fields

tblAnalysis
analysisID (PK)
analysisDescription
'any other related fields

tblProductAnalysis
prodAnalysisID (PK)
productID (FK)
analysisID (FK)
analysisOutcome
'any other related fields

Sample data,

[B][U]tblProducts[/U][/B]
productID    productDescription
1                Water
2                Wax

[B][U]tblAnalysis[/U][/B]
analysisID    analysisDescription
1                Mass
2                Boiling point

[B][U]tblProductAnalysis[/U][/B]
prodAnalysisID    productID    analysisID    analysisOutcome
1                  1            1            50
2                  1            2            100
3                  2            1            10
4                  2            2            86

Again, this is my idea of how your data should be organized. Might not fit in your business model. Maybe if you give a little bit more info. we might be able to give a better structure.

 

[plog]

D - none of the above. You were wrong coming out of the gate. You shouldn't have numerated field names (Analysis1, Analysis2, etc.) you use another table to define that many to many relationship.

You have a table of parts, you have a table of Analysi. To connect the two you need a junction table (http://en.wikipedia.org/wiki/Junction_table). Essentially its simple table that takes an ID from parts and an ID from Analysis.

Your tables would look like this:

Parts
PartID, PartName, PartField2, PartField3, ...
17, Brake Drum, , ,
91, Master Cylinder, ,

Analysis
AnalysisID, AnalysisName, AnalysisField2, AnalysisField3, ...
44, Heat Resistance, , ,
51, Corrosion, , ,

PartAnalysis
ID_Part, ID_Analysis
17, 44
17, 51
91, 51

From the information above, the Brake Drum did both tests and the Master Cylinder only did the Corrosive test.

That's how this should be structured.

(dang it, I was second by seconds and pr took the time to format his answer and add CODE tags as well.)

 

[Uncle Gizmo]

You may find this will provide some insight:

Here on AWF:
http://www.access-programmers.co.uk/forums/showthread.php?p=671226

Videos of the above:- (Off Site --- Requires Login)
http://msaccesshintsandtips.ning.com/profiles/blogs/excel-in-access-videos

 

[gemma-the-husky]

however, creating a single table with 5 columns for analysis results is much less worse than creating 5 different tables, I think

so, a slightly different viewpoint

The decision to put all the different analyses into an analysis table as outlined above also depends to some extent how mutually exclusive they are.

If you would never ever want to see the results for multiple analyses together then it may not matter. But if you do want to compare them, then it makes more sense to have all the analysis results in a single column - as otherwise you start having to develop multiple queries every time you want to do something.

Having multiple columns also may need a redesign if and when you decide you need a sixth analysis.

 

[ryetee]

Oops school boy error.
Got seduced by the User saying he wanted 5 analysis fields!!
So the above solutions cater for a many to many relationship.
Just one thing though each group of analysis fields are unique. So for group 1 they're can be many descriptions but they are all unique from group 2. How do I stop the user picking 5 from the same group?

 

[gemma-the-husky]

you would have a suitable unique index to prevent duplicates

 

[ryetee]

I have no idea what your business requirement is, but IMVHO both method you proposed is not the way. You might need three tables, like..

tblProducts
productID (PK)
productDescription
'any other related fields

tblAnalysis
analysisID (PK)
analysisDescription
'any other related fields

tblProductAnalysis
prodAnalysisID (PK)
productID (FK)
analysisID (FK)
analysisOutcome
'any other related fields

Sample data,

[B][U]tblProducts[/U][/B]
productID    productDescription
1                Water
2                Wax

[B][U]tblAnalysis[/U][/B]
analysisID    analysisDescription
1                Mass
2                Boiling point

[B][U]tblProductAnalysis[/U][/B]
prodAnalysisID    productID    analysisID    analysisOutcome
1                  1            1            50
2                  1            2            100
3                  2            1            10
4                  2            2            86

Again, this is my idea of how your data should be organized. Might not fit in your business model. Maybe if you give a little bit more info. we might be able to give a better structure.

Thanks to you and plog for your responses.

I now have further information. The user has notion of what he wants but no clear thought (guess we've all come across that before).
He wants to do some analysis on his stock. He knows a bit about what this analysis may be but again not everything although he thinks he'll want up to 5 different types of analysis. The company repairs things(!) and they repair old things. They keep stock of the main things that go wrong. They source these from all over the place. Now some parts are becoming scarce to source so analysis 1 would say be
1. coming to end of being able to source this but not sure when
2. nearing the end of life so bit more breathing space
3. last time I can buy this is such and such a date
4. there could be others
Now analysis 2 could depend upon analysis 2 such as
1. do i care it's coming/nearing to end of life - yes i have no other supplier/no I have another supplier/no I no longer need the part/etc
2. last time i can buy is yyyy/mm/dd
3.there could be others
Analysis 3 could depend on analysis 2
1. HAve a new supplier
2. Don't have a new supplier
3. Can use part xxx instead

etc etc. It's hard getting exactly what he wants but I'm getting that each different type of analysis has a unique set of analyses. I'm now going off a many to many relationship as part can only have 1 analysis 1 but analysis 1 can be on many parts (1 to many).
So I don't know which way to turn!!! Help!