Re: Database Normilzation/ERD Help needed

[Peter Brawley]

So is this it?

sampletypes( sampletypeID,name ) -- eg surface, core, etc

testtypes( testtypeID,name,etc )

samples( sampleID,sampletype references sampletypes(sampletypeID), datetime, property, takenby, xcoord, ycoord, etc... )

tests( testID,testtypeID references testtypes(testtypeID), sampleID references samples (sampleID), datetime, etc )

results( resultID,testID,etc )

To discover what needs changing with a proposed schema, write all the queries that will be needed against it, eg find what resultID 123 refers to...

SELECT r.result,t.datetime, tt.name, s.property, s.property, s.xcoord, s.ycoord, st.name
FROM result r
JOIN tests t ON r.testID=t.testID
JOIN testtypes tt on t.testtypeID=tt.testtypeID
JOIN samples s ON t.sampleID=s.sampleID
JOIN sampletypes st ON s.sampletypeID=st.sampletypeID
WHERE r.resultID=123;

PB