On 02/28/2016 06:09 AM, Aaron Christensen wrote:
On Sun, Feb 28, 2016 at 12:36 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
