Re: Designing tables based on user input and defined values

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>