[Wyz]

Hi All,

I am developing a database to store data from rock samples.

I need help with the best way to create relationship between tables.

I have 2 tables of different rock sources both with unique columns. "Core" and "Samples"

idCore
Depth
Well
CoreDiameter
...

idSamples
XCoord
YCoord
...

And both sources have the sames tests which result with the same data types. Lets call this table "Test"

idTest
Result1
Result2
...

Now For the relationships

Core - Test 1:1
Surface - Test 1:1

I only want to have one table which stores the Test data.

What is the best way to relate these tables so that I can easily identify which (core or sample) gave a particular test result.

Paul

[Pat Phelan]

This sounds like a homework question, and without the full assignment (and possibly knowing what had been covered in your class) we can only make a good guess. We can provide a correct and complete answer at the theoretical/logical level, a different answer at the physical level, and still have room left to argue about the details!

From a purely set-theory perspective, the only correct answer is to include a testid foreign key in both the core and the sample entities. The existance of a tuple within a core or a sample entity determines whether that test entity is related to the tuple that includes its primary key value, and this is the only correct answer from the entity relationship perspective.

There are lots more answers at the physical level. Do we want to go there?

-PatP

[Wyz]

Hi Pat,

Not a homework question. I work for an oil company. It is actually much bigger then this. Just used the 2 tables as examples. I actually have 5 sources of rocks which have the same Biostratigraphy testing done on them but each come from a different source and therefore have very different fields. The only thing that binds them together is the test results. I need to be able to query the test results and show which sources gave those results.

I found this example for vehicles from the design resource.

http://www.databaseanswers.org/data_...ture/index.htm

I guess my rocks are like the vehicle types: automobile and tractor. (Core and Sample)

But using this example. If I only had a PART_ID and looking up the database found the vehicle_id. How would I know which table to join Automobile or Tractor? I could loop through each table looking for the vehicle_id but I have 5 tables to loop through, then this gets messy.

Is this the best way to model this example.

Paul

[r937]

not loop through -- LEFT OUTER JOIN to

on the other hand, since the relationships are one-to-one you could put five foreign keys into the Test table

[Wyz]

r937- I don't think 5 foreign Keys in the test table is the best way to go...

Does anybody else want to comment here.

[r937]

Originally Posted by Wyz

r937- I don't think 5 foreign Keys in the test table is the best way to go...

Does anybody else want to comment here.

so you're, what, cutting me off from further comment?

why don't you think it's the way to go?

why bother posting the question if you know what you're going to do?

[Wyz]

Sorry R937. I didn't mean it that way.

thankyou for your help but I was asking what other people thought of your idea.

Do it as you have suggested - 5 Foreign Keys

or

as Pat has suggested - include a testid foreign key in both the core and the sample entities.

These are directly opposite to each other.

Also to add to the complication there with be more then one type of test table which will again have to be a separate table.

[Pat Phelan]

I'm sure that this is a radical concept, but how about we work on the actual problem instead of something that may resemble it? I think that we could give a lot better answers if we had a better understanding of what you are trying to do than we could by offering suggestions based on what you've posted and then trying to blindly stagger toward a correct solution as we find out more details!

I think from r937's suggestion that he's already headed toward a physical solution. The problem I see with that is that I always prefer to solve as many problems as I can at the logical level, then treat the physical level as simply a burden to be borne... This allows me to focus on the problem at a more abstract level, and to find a solution that is more elegant and easier for me to work with than a pure physical solution could be.

-PatP

[r937]

Originally Posted by Pat Phelan

I think from r937's suggestion that he's already headed toward a physical solution.

um, how shall i put this kindly... no

you put your FKs where you want and it's a logical design choice, but i put my FKs where i want and i'm headed for a physical solution? a burden to be borne? WTF??????

[r937]

Originally Posted by Wyz

Also to add to the complication there with be more then one type of test table which will again have to be a separate table.

whoa, whoa, wait a sec

somebody said "I only want to have one table which stores the Test data."

hey, that was you! what's going on here?

[mike_bike_kite]

Can I join the party?

Specimens                     -- used to be called Rocks
	specimenId
	specimenType
        specimenName
	anyOtherCommonFields
	...

RockCores
	specimenId
	depth
	well
	coreDiameter
	...

RockSamples
	specimenId
	xCoOrd
	yCoOrd
	...

Tests
	testId
	testName
	testDesc
        result
	...

Results
	specimenId
	testId
	date
	tester
	...

Mike

EDIT added rockName and result fields. Changed name of table Rocks to Specimens

[blindman]

Results should be tied to Rock Cores or Rock Samples, but not Rocks...

[mike_bike_kite]

Originally Posted by blindman
Results should be tied to Rock Cores or Rock Samples, but not Rocks...

From the point of view of being able to use FK then you're quite correct but from the point of view of being able to produce simple applications that are easy to write for all types then I (personally) prefer just joining to the main table. So if you were looking for all the Rocks (?) that had the result X from test Y the sql would be:

select    ro.specimenType, ro.specimenName
from      Specimens s, Results re
where    re.testId = Y
            and re.result = X
            and s.specimenId = re.specimenId

It would be fine to join the Results table to the RockSample, RockCore ... tables but it means the amount of SQL coding would be longer. Obviously it can soon become a religious debate saying what approach to database design is best but he did ask for some other suggestions for how to approach the problem and this approach would be mine.

Hope all is well.

Mike

[Wyz]

Originally Posted by R937

whoa, whoa, wait a sec

somebody said "I only want to have one table which stores the Test data."

hey, that was you! what's going on here?

Gee you have some agro issues... I just joined this forum. I hope this guy isn't a representation of the general poster.

I did not see that 1, 2 or 100 tests would not change the scope of the answers entirely.

I just wanted to take small steps and simplify the question to a simple relationship and how is best way to structure it. Every example on the web uses simple analogues but this I feel is a little outside the square, having two separate tables which are not directly related fill one table of exactly the same data.

[Wyz]

Originally Posted by mike_bike_kite

From the point of view of being able to use FK then you're quite correct but from the point of view of being able to produce simple applications that are easy to write for all types then I (personally) prefer just joining to the main table. So if you were looking for all the Rocks (?) that had the result X from test Y the sql would be:

select    ro.rockType, ro.rockName
from      Rocks ro, Results re
where    re.testId = Y
            and re.result = X
            and ro.rockId = re.rockId

It would be fine to join the Results table to the RockSample, RockCore ... tables but it means the amount of SQL coding would be longer. Obviously it can soon become a religious debate saying what approach to database design is best but he did ask for some other suggestions for how to approach the problem and this approach would be mine.

Hope all is well.

Mike

Thankyou for the help Mike,

I think this is where I heading. Creating a overlying Rocks table to give each rock be that a core or sample a unique id.

But a question. In the rocksamples and rockcores tables you only have rockid, would i also have a rocksampleid and rockcoresid or just use the rockid?

P.