Re: Designing tables based on user input and defined values - Mailing list pgsql-general

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