Re: Designing tables based on user input and defined values

On Sat, Feb 27, 2016 at 10:36 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.