Re: Designing tables based on user input and defined values

On 02/27/2016 09:19 PM, Aaron Christensen wrote:

There is somewhat a method to this madness :).  There isn't a formula
that determines outcome.  They will just be arbitrary values that I assign.

Obviously, I'm new to SQL but I'm trying to understand your suggestion.
It appears that table Final has the composite/primary keys of goal and
size which will be foreign keyed to table User.   How exactly does the
user submit/store his goal/size and be assigned an outcome if the User
table is using FKs  for goal/size?  It seems backwards to me.

Well there a some unanswered questions, answers to which will shape the ultimate design:

Who actually creates the relationship between goal/size and outcome, the user or you?

Can a user have more than one combination of goal/size?

As to how the user picks their goal/size, that is more an application question. What the relationship between user and final does is ensure that a user can only select a goal/size combination that exists, which I assumed is what you where looking for when you mentioned a lookup table. If I misunderstood then maybe the answers to the above questions will clarify.

On Feb 27, 2016 7:04 PM, "Adrian Klaver" <adrian.klaver@aklaver.com

<mailto:adrian.klaver@aklaver.com>> wrote:

    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>

        <mailto: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>

        <mailto:adrian.klaver@aklaver.com
        <mailto:adrian.klaver@aklaver.com>>




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

--
Adrian Klaver
adrian.klaver@aklaver.com