Re: Designing tables based on user input and defined values - Mailing list 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.