Re: Designing tables based on user input and defined values

On 02/27/2016 03:12 PM, Aaron Christensen wrote:

Hi Adrian,

Thank you for responding with the SQL code.  However, outcome cannot be
a primary key because outcome values will be duplicates in some
instances.  I am not sure how else to have a lookup table that stores
static values.

Well first is there a method to the madness:)?

In other words is the choice of an outcome arbitrary or is there some calculation that goes into it?

Otherwise, something like?:

test=> create table final(goal varchar, size varchar, outcome int, PRIMARY KEY(goal, size));

test=> create table user_tbl(user_id int PRIMARY KEY, user_name varchar,  goal varchar, size varchar, CONSTRAINT g_s_fk  FOREIGN KEY (goal, size)  REFERENCES final(goal, size));


test=> \d final
          Table "public.final"
 Column  |       Type        | Modifiers
---------+-------------------+-----------
 goal    | character varying | not null
 size    | character varying | not null
 outcome | integer           |
Indexes:
    "final_pkey" PRIMARY KEY, btree (goal, size)
Referenced by:
    TABLE "user_tbl" CONSTRAINT "g_s_fk" FOREIGN KEY (goal, size) REFERENCES final(goal, size)

test=> \d user_tbl
          Table "public.user_tbl"
  Column   |       Type        | Modifiers
-----------+-------------------+-----------
 user_id   | integer           | not null
 user_name | character varying |
 goal      | character varying |
 size      | character varying |
Indexes:
    "user_tbl_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
    "g_s_fk" FOREIGN KEY (goal, size) REFERENCES final(goal, size)

Thanks!
Aaron

On Sat, Feb 27, 2016 at 5:15 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

    On 02/27/2016 01:15 PM, Aaron Christensen wrote:

        Hello,

        I am trying to figure out the correct way to design the database
        table
        to support the following situation.

        To start, I have an Excel spreadsheet that maps particular
        combinations
        of Goal and Size to an Outcome.  Goal choices are "Long",
        "Average", and
        "Short".  Size choices are "Big", "Medium", and "Small".  The
        designated
        Outcome for each goal/size combination are number values between
        12 and
        20.  Please refer to attachment "goalSizeExcel.pdf" for the Excel
        spreadsheet version.

        In order to use this data in the database, I converted it to an SQL
        table with attributes "Goal", "Size", and "OUTCOME".  "Goal" and
        "Size"
        serve as composite primary keys.  Please refer to attachment
        "TableFinal.pdf" for the illustration.

        Please refer to "UserOutcome.jpg" for the ER diagram.  The user
        inputs
        his name, goal, and size.  Based on his goal and size
        combination, he is
        assigned a particular "outcome".

        I am not exactly sure if my attached ER diagram is the correct
        way to
        model this.  I don't want to add a UserId [FK] to table Final
        because
        table Final is supposed to serve as a lookup or reference table
        (I am
        not sure of the correct terminology).

        Please advise if I am on the right track or if I should follow a
        different design.  I intend to have a few other lookup/reference
        tables
        that will serve a similar purpose.


     >From a quick look it seems to me that outcome is the primary key
    to goal and size, so

    CREATE TABLE final (
       outcome int PRIMARY KEY,
       goal varchar,
       size varchar
    )

    CREATE TABLE user (
    name varchar,
    outcome_fk int REFERENCES final(outcome) ON ...
    )



        Thank you!
        Aaron








    --
    Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com