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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
