AnsweredAssumed Answered

Table Design and Relationship help

Question asked by sidetrack_ca on Oct 2, 2017
Latest reply on Oct 2, 2017 by philmodjunk

Hi everyone,

 

I am in need of some design advice.  My gut says that I am making this overly complex and its confusing me, but I wanted to take it to the community for some unbiased opinions.  I want to apologize up front for the length of this post as I don’t want to leave anything out.

 

Background:

I am creating an intranet style app that controls different aspects of employee needs for my company (i.e. vacation requests, reimbursement forms, etc.)  One of the biggest portions of this app is the commission entry worksheet for our sales team.  this entry screen involves the sales rep entering in data about a sale that was completed.  The layout screen has two parts, a top part where the sales rep enters the details about a particular sale (i.e. Sale Date, product sold, etc.) the bottom is a portal where the sales that have been entered for the current month are displayed. The sales rep enters the particulars for a sale and presses the “save” button.  This saves the record and a row shows in the portal the details of the sale. If the sales rep needs to make any changes to the record they saved, they can click on the portal row and a popover window will display allowing them to change the information and re-save the record.  When the month is complete and the sales rep is ready to submit the records to the supervisor, they press the submit button at the end of the document and the records are submitted. This table will only contain approximately 10-15 new records per month in total.

 

Struggle:

I am having issues with understanding the connection between the tables with relationships in mind. In particular with the products.master table and the transaction.header / transaction.detail tables.

 

I have a layout called products.master.entry that allows the sales manager to enter products that we sell. When the sales rep enters what he sold, I will need information from the product.master table (where all the product information is stored), like product.name, product.cost and product.price.  this information is used to calculate commissions for the sales rep.  I need the product information from the products table, but I do not want to enter more items in the products table.  I only want the information and I want it saved in the transaction.detail table. I have included a relationship graph and a copy of the product.master entry and commission.entry layouts for reference.

 

I appreciate any design advice, way to simplify, or just any comment good or bad for the noob here.  I want to do this the right way and hopefully prevent some pitfalls in the future.

 

Thanks in advance for your help and advice!


Rob

DB Relationships.png

Product Master Entry Layout.png

Commission Entry Layout.png

Outcomes