Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Oct 2007
    Posts
    9

    Relationship Help

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Oct 2007
    Posts
    9
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Posts
    9
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2007
    Posts
    9
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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??????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Can I join the party?

    Code:
    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
    Last edited by mike_bike_kite; 10-23-07 at 16:11.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Results should be tied to Rock Cores or Rock Samples, but not Rocks...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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:
    Code:
    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
    Last edited by mike_bike_kite; 10-23-07 at 16:12.

  14. #14
    Join Date
    Oct 2007
    Posts
    9
    Quote 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.

  15. #15
    Join Date
    Oct 2007
    Posts
    9
    Quote 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:
    Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •