Re: Designing tables based on user input and defined values

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