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.kla...@aklaver.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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
