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