Обсуждение: Designing tables based on user input and defined values
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.
Thank you!
Aaron
Вложения
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
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.klaver@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.klaver@aklaver.com
On 02/27/2016 03:12 PM, Aaron Christensen wrote:
> 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.
Well first is there a method to the madness:)?
In other words is the choice of an outcome arbitrary or is there some
calculation that goes into it?
Otherwise, something like?:
test=> create table final(goal varchar, size varchar, outcome int,
PRIMARY KEY(goal, size));
test=> create table user_tbl(user_id int PRIMARY KEY, user_name varchar,
goal varchar, size varchar, CONSTRAINT g_s_fk FOREIGN KEY (goal,
size) REFERENCES final(goal, size));
test=> \d final
Table "public.final"
Column | Type | Modifiers
---------+-------------------+-----------
goal | character varying | not null
size | character varying | not null
outcome | integer |
Indexes:
"final_pkey" PRIMARY KEY, btree (goal, size)
Referenced by:
TABLE "user_tbl" CONSTRAINT "g_s_fk" FOREIGN KEY (goal, size)
REFERENCES final(goal, size)
test=> \d user_tbl
Table "public.user_tbl"
Column | Type | Modifiers
-----------+-------------------+-----------
user_id | integer | not null
user_name | character varying |
goal | character varying |
size | character varying |
Indexes:
"user_tbl_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"g_s_fk" FOREIGN KEY (goal, size) REFERENCES final(goal, size)
>
> Thanks!
> Aaron
>
> On Sat, Feb 27, 2016 at 5:15 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
There is somewhat a method to this madness :). There isn't a formula that determines outcome. They will just be arbitrary values that I assign.
Obviously, I'm new to SQL but I'm trying to understand your suggestion. It appears that table Final has the composite/primary keys of goal and size which will be foreign keyed to table User. How exactly does the user submit/store his goal/size and be assigned an outcome if the User table is using FKs for goal/size? It seems backwards to me.
On Feb 27, 2016 7:04 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
On 02/27/2016 03:12 PM, Aaron Christensen wrote: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.
Well first is there a method to the madness:)?
In other words is the choice of an outcome arbitrary or is there some calculation that goes into it?
Otherwise, something like?:
test=> create table final(goal varchar, size varchar, outcome int, PRIMARY KEY(goal, size));
test=> create table user_tbl(user_id int PRIMARY KEY, user_name varchar, goal varchar, size varchar, CONSTRAINT g_s_fk FOREIGN KEY (goal, size) REFERENCES final(goal, size));
test=> \d final
Table "public.final"
Column | Type | Modifiers
---------+-------------------+-----------
goal | character varying | not null
size | character varying | not null
outcome | integer |
Indexes:
"final_pkey" PRIMARY KEY, btree (goal, size)
Referenced by:
TABLE "user_tbl" CONSTRAINT "g_s_fk" FOREIGN KEY (goal, size) REFERENCES final(goal, size)
test=> \d user_tbl
Table "public.user_tbl"
Column | Type | Modifiers
-----------+-------------------+-----------
user_id | integer | not null
user_name | character varying |
goal | character varying |
size | character varying |
Indexes:
"user_tbl_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"g_s_fk" FOREIGN KEY (goal, size) REFERENCES final(goal, size)
Thanks!
Aaron
On Sat, Feb 27, 2016 at 5:15 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 02/27/2016 09:19 PM, Aaron Christensen wrote: > There is somewhat a method to this madness :). There isn't a formula > that determines outcome. They will just be arbitrary values that I assign. > > Obviously, I'm new to SQL but I'm trying to understand your suggestion. > It appears that table Final has the composite/primary keys of goal and > size which will be foreign keyed to table User. How exactly does the > user submit/store his goal/size and be assigned an outcome if the User > table is using FKs for goal/size? It seems backwards to me. Well there a some unanswered questions, answers to which will shape the ultimate design: Who actually creates the relationship between goal/size and outcome, the user or you? Can a user have more than one combination of goal/size? As to how the user picks their goal/size, that is more an application question. What the relationship between user and final does is ensure that a user can only select a goal/size combination that exists, which I assumed is what you where looking for when you mentioned a lookup table. If I misunderstood then maybe the answers to the above questions will clarify. > > On Feb 27, 2016 7:04 PM, "Adrian Klaver" <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 02/27/2016 03:12 PM, Aaron Christensen wrote: > > 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. > > > Well first is there a method to the madness:)? > > In other words is the choice of an outcome arbitrary or is there > some calculation that goes into it? > > Otherwise, something like?: > > test=> create table final(goal varchar, size varchar, outcome int, > PRIMARY KEY(goal, size)); > > test=> create table user_tbl(user_id int PRIMARY KEY, user_name > varchar, goal varchar, size varchar, CONSTRAINT g_s_fk FOREIGN KEY > (goal, size) REFERENCES final(goal, size)); > > > test=> \d final > Table "public.final" > Column | Type | Modifiers > ---------+-------------------+----------- > goal | character varying | not null > size | character varying | not null > outcome | integer | > Indexes: > "final_pkey" PRIMARY KEY, btree (goal, size) > Referenced by: > TABLE "user_tbl" CONSTRAINT "g_s_fk" FOREIGN KEY (goal, size) > REFERENCES final(goal, size) > > test=> \d user_tbl > Table "public.user_tbl" > Column | Type | Modifiers > -----------+-------------------+----------- > user_id | integer | not null > user_name | character varying | > goal | character varying | > size | character varying | > Indexes: > "user_tbl_pkey" PRIMARY KEY, btree (user_id) > Foreign-key constraints: > "g_s_fk" FOREIGN KEY (goal, size) REFERENCES final(goal, size) > > > > > > Thanks! > Aaron > > On Sat, Feb 27, 2016 at 5:15 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
On 02/27/2016 09:19 PM, Aaron Christensen wrote:There is somewhat a method to this madness :). There isn't a formula
that determines outcome. They will just be arbitrary values that I assign.
Obviously, I'm new to SQL but I'm trying to understand your suggestion.
It appears that table Final has the composite/primary keys of goal and
size which will be foreign keyed to table User. How exactly does the
user submit/store his goal/size and be assigned an outcome if the User
table is using FKs for goal/size? It seems backwards to me.
Well there a some unanswered questions, answers to which will shape the ultimate design:
Who actually creates the relationship between goal/size and outcome, the user or you?
Can a user have more than one combination of goal/size?
As to how the user picks their goal/size, that is more an application question. What the relationship between user and final does is ensure that a user can only select a goal/size combination that exists, which I assumed is what you where looking for when you mentioned a lookup table. If I misunderstood then maybe the answers to the above questions will clarify.
To be a bit more blunt - we are only dealing with 4 fields here so if it is unclear how to proceed its not because the model is complex: its because it is unknown what is supposed to be happening in the first place.
Another question not yet put forth is how do you want to deal with change? It would be wise to assume that the chosen outcome value could change in the future in which case do you need to record the value permanently as of the time the record was created or is changing pre-existing data correct?
By reading the only process description provided:
"""
The user inputs his name, goal, and size. Based on his goal and size combination, he is assigned a particular "outcome".
"""
I would consider writing something like the following pseudo-code:
CREATE TABLE user_outcome (username text, goal text, size text, outcome integer)
PRIMARY KEY username
CREATE FUNCTION compute_outcome(username, goal, size) RETURNS integer
AS $$
IF EXISTS(SELECT username FROM user_outcome WHERE username = username) THEN
RAISE EXCEPTION 'User % Already Submitted An Outcome', username
END IF
INSERT INTO user_outcome(username, goal, size, outcome)
WITH goal_size_lookup (goal, size, outcome) AS (
VALUES ('short','small',20), (etc)
)
SELECT username, goal, size, outcome
FROM goal_size_lookup
WHERE goal = goal AND size = size
RETURNING outcome;
$$
Thus the user, at the time of submission, is assigned an outcome. That outcome never changes even if the computation of the outcomes changes.
You can choose to store the goal_size_lookup data is a persistent table if desired or needed but even should you do so you'd need to consider whether you really want there to be a PK/FK relationship. The function approach hides all this detail nicely and lets you write the procedural logic you will need to actual use whatever model is implemented. And in fact such a procedure will likely tell you exactly what said model needs to look like.
And you need to decide which fields go into making the PK for the result table. (username), or (username, goal, size), or (user, goal, size, timestamp) - the last being the case if you want to allow new submissions while maintaining a record of previous ones (if you don't care about history you simply delete, or error, upon re-submission).
David J.
On Sun, Feb 28, 2016 at 12:36 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/27/2016 09:19 PM, Aaron Christensen wrote:There is somewhat a method to this madness :). There isn't a formula
that determines outcome. They will just be arbitrary values that I assign.
Obviously, I'm new to SQL but I'm trying to understand your suggestion.
It appears that table Final has the composite/primary keys of goal and
size which will be foreign keyed to table User. How exactly does the
user submit/store his goal/size and be assigned an outcome if the User
table is using FKs for goal/size? It seems backwards to me.
Well there a some unanswered questions, answers to which will shape the ultimate design:
Who actually creates the relationship between goal/size and outcome, the user or you?
Can a user have more than one combination of goal/size?
As to how the user picks their goal/size, that is more an application question. What the relationship between user and final does is ensure that a user can only select a goal/size combination that exists, which I assumed is what you where looking for when you mentioned a lookup table. If I misunderstood then maybe the answers to the above questions will clarify.
It's not that you're misunderstanding, it's that I'm doing a horrible job describing my question.
For answers to your questions:
I will be creating the relationship between goal/size and outcome. The user can only provide their goal/size on any particular date. Based on their goal size, I will assign to them the corresponding outcome which will be used as an input to some formula. Any user can have one or multiple goal/size combinations. That is correct, the user will only be able to select preexisting goals and sizes.
On Feb 27, 2016 7:04 PM, "Adrian Klaver" <adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com<mailto:adrian.klaver@aklaver.com>> wrote:
On 02/27/2016 03:12 PM, Aaron Christensen wrote:
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.
Well first is there a method to the madness:)?
In other words is the choice of an outcome arbitrary or is there
some calculation that goes into it?
Otherwise, something like?:
test=> create table final(goal varchar, size varchar, outcome int,
PRIMARY KEY(goal, size));
test=> create table user_tbl(user_id int PRIMARY KEY, user_name
varchar, goal varchar, size varchar, CONSTRAINT g_s_fk FOREIGN KEY
(goal, size) REFERENCES final(goal, size));
test=> \d final
Table "public.final"
Column | Type | Modifiers
---------+-------------------+-----------
goal | character varying | not null
size | character varying | not null
outcome | integer |
Indexes:
"final_pkey" PRIMARY KEY, btree (goal, size)
Referenced by:
TABLE "user_tbl" CONSTRAINT "g_s_fk" FOREIGN KEY (goal, size)
REFERENCES final(goal, size)
test=> \d user_tbl
Table "public.user_tbl"
Column | Type | Modifiers
-----------+-------------------+-----------
user_id | integer | not null
user_name | character varying |
goal | character varying |
size | character varying |
Indexes:
"user_tbl_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"g_s_fk" FOREIGN KEY (goal, size) REFERENCES final(goal, size)
Thanks!
Aaron
On Sat, Feb 27, 2016 at 5:15 PM, Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><mailto:adrian.klaver@aklaver.com
<mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
<mailto:adrian.klaver@aklaver.com>>
--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Sun, Feb 28, 2016 at 1:15 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On 02/27/2016 09:19 PM, Aaron Christensen wrote:There is somewhat a method to this madness :). There isn't a formula
that determines outcome. They will just be arbitrary values that I assign.
Obviously, I'm new to SQL but I'm trying to understand your suggestion.
It appears that table Final has the composite/primary keys of goal and
size which will be foreign keyed to table User. How exactly does the
user submit/store his goal/size and be assigned an outcome if the User
table is using FKs for goal/size? It seems backwards to me.
Well there a some unanswered questions, answers to which will shape the ultimate design:
Who actually creates the relationship between goal/size and outcome, the user or you?
Can a user have more than one combination of goal/size?
As to how the user picks their goal/size, that is more an application question. What the relationship between user and final does is ensure that a user can only select a goal/size combination that exists, which I assumed is what you where looking for when you mentioned a lookup table. If I misunderstood then maybe the answers to the above questions will clarify.To be a bit more blunt - we are only dealing with 4 fields here so if it is unclear how to proceed its not because the model is complex: its because it is unknown what is supposed to be happening in the first place.
Hi David,
You are correct. I don't think it's supposed to be very complex/complicated and I imagine that my question should've been answered within the first one or two emails. And the reason is because I am doing a really bad job at trying to describe my question. (I responded with more information to Adrian: I will be creating the relationship between goal/size and outcome. The user can only provide their goal/size on any particular date. Based on their goal size, I will assign to them the corresponding outcome which will be used as an input to some formula. Any user can have one or multiple goal/size combinations. The user will only be able to select preexisting goals and sizes.)
Another question not yet put forth is how do you want to deal with change? It would be wise to assume that the chosen outcome value could change in the future in which case do you need to record the value permanently as of the time the record was created or is changing pre-existing data correct?
This is an interesting point. At the time the user inputs his one or many goal/size combinations, he will be assigned a particular outcome. Once the user is assigned the outcome, it will never change for that particular entry. In the future, however, the outcome in the lookup table can change and any newly inputted goal/size combinations will be assigned those new outcomes. So, the record will be permanently stored with whatever the outcome is at that point in time.
Unfortunately, I am not well versed with SQL so it will take me a bit to digest the code you provided. Thank you for chiming in!
By reading the only process description provided:"""The user inputs his name, goal, and size. Based on his goal and size combination, he is assigned a particular "outcome"."""I would consider writing something like the following pseudo-code:CREATE TABLE user_outcome (username text, goal text, size text, outcome integer)PRIMARY KEY usernameCREATE FUNCTION compute_outcome(username, goal, size) RETURNS integerAS $$IF EXISTS(SELECT username FROM user_outcome WHERE username = username) THENRAISE EXCEPTION 'User % Already Submitted An Outcome', usernameEND IFINSERT INTO user_outcome(username, goal, size, outcome)WITH goal_size_lookup (goal, size, outcome) AS (VALUES ('short','small',20), (etc))SELECT username, goal, size, outcomeFROM goal_size_lookupWHERE goal = goal AND size = sizeRETURNING outcome;$$Thus the user, at the time of submission, is assigned an outcome. That outcome never changes even if the computation of the outcomes changes.You can choose to store the goal_size_lookup data is a persistent table if desired or needed but even should you do so you'd need to consider whether you really want there to be a PK/FK relationship. The function approach hides all this detail nicely and lets you write the procedural logic you will need to actual use whatever model is implemented. And in fact such a procedure will likely tell you exactly what said model needs to look like.And you need to decide which fields go into making the PK for the result table. (username), or (username, goal, size), or (user, goal, size, timestamp) - the last being the case if you want to allow new submissions while maintaining a record of previous ones (if you don't care about history you simply delete, or error, upon re-submission).David J.
On 02/28/2016 06:09 AM, Aaron Christensen wrote: > > > On Sun, Feb 28, 2016 at 12:36 AM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 02/27/2016 09:19 PM, Aaron Christensen wrote: > > There is somewhat a method to this madness :). There isn't a > formula > that determines outcome. They will just be arbitrary values > that I assign. > > Obviously, I'm new to SQL but I'm trying to understand your > suggestion. > It appears that table Final has the composite/primary keys of > goal and > size which will be foreign keyed to table User. How exactly > does the > user submit/store his goal/size and be assigned an outcome if > the User > table is using FKs for goal/size? It seems backwards to me. > > > Well there a some unanswered questions, answers to which will shape > the ultimate design: > > Who actually creates the relationship between goal/size and outcome, > the user or you? > > Can a user have more than one combination of goal/size? > > As to how the user picks their goal/size, that is more an > application question. What the relationship between user and final > does is ensure that a user can only select a goal/size combination > that exists, which I assumed is what you where looking for when you > mentioned a lookup table. If I misunderstood then maybe the answers > to the above questions will clarify. > > > > It's not that you're misunderstanding, it's that I'm doing a horrible > job describing my question. I have found that my best design tool is a legal pad and a pencil/pen. FYI, I am not a programmer by training, so my methods are based on observation of others more then any formal guidelines. I start by writing out an outline description of the information I want to create/handle and how the various pieces of information relate to each. In this stage I look for units of information, that is data that looks like it naturally belongs together. Then I look for how the various units potentially relate to each other. Then I start doing what ifs on the data and the relationships e.g. the point David brought up about freezing an outcome value at a point in time. Basically I try to become the end user and anticipate what information they need and how they will interact with it. This is the hardest part as users, bless their hearts, will do things that never entered your mind when designing the flow of information. Generally this an iterative process where the first drafts lead to revisions in layout that then need more thought. At some point the close enough rule applies and I actually create the objects in the database and start throwing actual data at them for testing purposes and validation. For me it is less confusing to do the design in the abstract on paper, to work out the big picture, then trying to do it in the database with real objects. Mainly because dealing with real objects means dealing with all the low level details and corresponding errors when you don't. > > For answers to your questions: > I will be creating the relationship between goal/size and outcome. The > user can only provide their goal/size on any particular date. Based on > their goal size, I will assign to them the corresponding outcome which > will be used as an input to some formula. Any user can have one or > multiple goal/size combinations. That is correct, the user will only be > able to select preexisting goals and sizes. > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, Feb 28, 2016 at 09:09:02AM -0800, Adrian Klaver wrote:
> I have found that my best design tool is a legal pad and a pencil/pen.
http://www.howtomakesenseofanymess.com/
Karsten Hilbert
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 02/28/2016 09:20 AM, Karsten Hilbert wrote: > On Sun, Feb 28, 2016 at 09:09:02AM -0800, Adrian Klaver wrote: > >> I have found that my best design tool is a legal pad and a pencil/pen. > > http://www.howtomakesenseofanymess.com/ Wow, that is an interesting link. Have just skimmed it at this point, but I did find this: http://www.howtomakesenseofanymess.com/chapter/7/page/130/make-sense-yet/ which says what I was trying to say in an even more concise and thought out manner. > > Karsten Hilbert > -- Adrian Klaver adrian.klaver@aklaver.com