On Sun, Feb 28, 2016 at 12:36 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx<mailto:adrian.klaver@xxxxxxxxxxx<mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx><mailto:adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
<mailto:adrian.klaver@xxxxxxxxxxx>>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
