Re: Designing tables based on user input and defined values

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.
On Feb 27, 2016 7:04 PM, "Adrian Klaver" <adrian(dot)klaver(at)aklaver(dot)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(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
>