Re: Designing tables based on user input and defined values

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@aklaver.com