Re: Designing tables based on user input and defined values

On Sun, Feb 28, 2016 at 12:36 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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.
>

It's not that you're misunderstanding, it's that I'm doing a horrible job
describing my question.

For answers to your questions:
I will be creating the relationship between goal/size and outcome. The
user can only provide their goal/size on any particular date. Based on
their goal size, I will assign to them the corresponding outcome which will
be used as an input to some formula. Any user can have one or multiple
goal/size combinations. That is correct, the user will only be able to
select preexisting goals and sizes.

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