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.
Thanks! Aaron On Sat, Feb 27, 2016 at 5:15 PM, Adrian Klaver <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 >
