Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: simple table design question

    let's say i have a table of chemicals...let's call that table "Chemicals"

    Chemicals has a handful of fields

    field #1: id (integer)--primary key
    field #2: chemical_name (varchar2(50))
    field #3: vendor (varchar2(50))
    field #4: formula_weight (number(5,3))

    there's another table called "Test_Tubes"
    as you might guess, this table holds information on a collection of test-tubes.

    each test-tube is capable of holding any number of chemicals found in the Chemicals table. e.g., one test-tube may hold only 1 chemical...another test-tube may hold 55 chemicals.

    question: how would i set up the Test_Tubes table so that each record in this table is capable of referring back to n-number of records within the Chemicals table, where n is a non-negative integer?


    tyvm for any info!

  2. #2
    Join Date
    Mar 2004
    Posts
    6

    Re: simple table design question

    Originally posted by the_busy_ant
    let's say i have a table of chemicals...let's call that table "Chemicals"

    Chemicals has a handful of fields

    field #1: id (integer)--primary key
    field #2: chemical_name (varchar2(50))
    field #3: vendor (varchar2(50))
    field #4: formula_weight (number(5,3))

    there's another table called "Test_Tubes"
    as you might guess, this table holds information on a collection of test-tubes.

    each test-tube is capable of holding any number of chemicals found in the Chemicals table. e.g., one test-tube may hold only 1 chemical...another test-tube may hold 55 chemicals.

    question: how would i set up the Test_Tubes table so that each record in this table is capable of referring back to n-number of records within the Chemicals table, where n is a non-negative integer?


    tyvm for any info!
    okay, here's a suggestion for myself

    the table called "Chemicals" stays the same
    The table called "Test_Tubes" looks something like this:

    field #1: test_tube_id (integer)--primary key
    field #2: creation_date (date)
    field #3: sample_volume (integer)

    then there is a THIRD table, called Chemical_samples

    field #1: chemical_samples_id (integer) --primary key
    field #2: chemicals_id--foreign key relating back to the Chemicals table
    field #3: test_tube_id--foreign key relating back to the test_tube table
    field #4: chemical_concentration (integer)

    you wind up with a HUGE Chemical_samples table, but this is the only thing that makes sense to me so far.

    Is there a more elegant way?

    THX.

  3. #3
    Join Date
    May 2003
    Posts
    58

    Re: simple table design question

    i don't think a third table is necessary. chemical table will have testtube id as foreign key and that solves the problem




    Originally posted by the_busy_ant
    okay, here's a suggestion for myself

    the table called "Chemicals" stays the same
    The table called "Test_Tubes" looks something like this:

    field #1: test_tube_id (integer)--primary key
    field #2: creation_date (date)
    field #3: sample_volume (integer)

    then there is a THIRD table, called Chemical_samples

    field #1: chemical_samples_id (integer) --primary key
    field #2: chemicals_id--foreign key relating back to the Chemicals table
    field #3: test_tube_id--foreign key relating back to the test_tube table
    field #4: chemical_concentration (integer)

    you wind up with a HUGE Chemical_samples table, but this is the only thing that makes sense to me so far.

    Is there a more elegant way?

    THX.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296

    Re: simple table design question

    Originally posted by the_busy_ant
    okay, here's a suggestion for myself

    the table called "Chemicals" stays the same
    The table called "Test_Tubes" looks something like this:

    field #1: test_tube_id (integer)--primary key
    field #2: creation_date (date)
    field #3: sample_volume (integer)

    then there is a THIRD table, called Chemical_samples

    field #1: chemical_samples_id (integer) --primary key
    field #2: chemicals_id--foreign key relating back to the Chemicals table
    field #3: test_tube_id--foreign key relating back to the test_tube table
    field #4: chemical_concentration (integer)

    THX.
    That is the best way if you want a check on test-tubes.

    Now you have a check for valid chemicals, valid test-tubes, and the
    contents of those tubes.

    PK would be fields 1,2,3 from chemical_samples

    You always want to break everything down into it's smallest components.
    With 3 tables you simplify and minimize your code.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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