Re: Designing tables based on user input and defined values

On Sun, Feb 28, 2016 at 1:15 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

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

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