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