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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com