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>> 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>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
