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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Feb 27, 2016 at 10:36 PM, 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.

​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 Sun, Feb 28, 2016 at 1:15 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sat, Feb 27, 2016 at 10:36 PM, 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.

​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!

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general