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

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