Re: Designing tables based on user input and defined values - Mailing list pgsql-general

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