RE: table structure

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: rpw (rpw_at_discussions.microsoft.com)
Date: 06/16/04 

Date: Wed, 16 Jun 2004 14:58:01 -0700

Hi Patti,

Table design is a good forum for your question – you should always have a good, sound table design before developing forms. And you have done a nice job of stating your questions too.

Moving on now….

For questions 1-3, with a different table setup all of this could be easy, but I wouldn’t want to suggest that you do something that broke your business rules. I will offer a table structure, then you must decide if it will work or not. Keep your company and schedule tables the same, but only have one transaction table.

tblTransaction
   TransID PK
   SellingCompanyID FK
   PurchasingCompanyID FK
   ScheduleID FK
   TransDate
   TransHourEnding
   MW
   Price
   Losses 'see comment below about this....

I may be wrong, but it looks like one company can transact (sell or purchase) with any other company, thereby creating a many-to-many relationship. This new transaction table serves as the junction table for the M:M relationship. (To create this, in the Relationships window you would show the company table twice and drag the first version’s CompanyID to the SellingCompanyID field and the second version’s CompanyID to the PurchasingComapanyID field.)

    1. Base a form on the above table and your scheduler can enter an entire day’s worth of transactions in one sitting.

    2. By keeping each transaction separate, you can use a query to group them together by company, schedule, date, etc. This table layout doen’t allow for a Tag#, but if there are no rules for the creation of a “TagNumber”, you could just combine the TransDate with the two CompanyID’s in a text field on a report or form. You wouldn’t need to store it in a table because it would be the same every time you opened the report/form.

    3. With this design you can query for each hour, yes.

    4. With this setup, you would not need separate tables for sales and purchases, you could use queries to view who sold what to whom and when (or alternatively, who bought what from whom and when).
 
"Losses" - If this is a calculated field, then consider not storing it in a table. Typically, a calculated field will always arrive at the same calculation so you don't need to store it. You just put a textbox on a form or report to display the calculation.

Hope this helps. 

-- 
rpw
"Patti" wrote:
> I don't know if I should be asking this question in the 
> table structure section or forms design, but I'll start 
> here.  I am in the process of creating a database for 
> energy sales and purchases.  I currently have four tables 
> created:
> 
> TblCompany
> CompanyID		PK
> CompanyName
> 
> 
> TblScheduleType
> SchedID		PK
> SchedName
> 
> 
> TblTransaction
> TransID		PK
> TransHourEnding	(0100-2400 military time )
> MW 			(# of megawatts per hour)
> Price			(price/mw)
> Losses		(MW loss per hour)
> 
> 
> TblMasterTransaction
> MtransID		PK
> MtransDate
> MtransTag#		(an identifier assigned to both 
> the purchasing and selling entities)
> CompanyID		FK
> SchedID		FK
> TransID		FK
> 
> 
> My question(s) are regarding the 'Hour-ending' field.  
> 
> 1.	I need to keep track of the total MW sold for 
> each hour, the company and the schedule type.  The Load 
> Scheduler making the transaction is not going to want to 
> enter every hour for every transaction he makes each 
> day.  It would be too time consuming.  I know I'm jumping 
> ahead, but I'm visualizing a form that will have all the 
> hours listed for the day and the scheduler can just 
> cursor down to the hour(s) in question and enter the MW 
> and $$. Is this achievable, possibly through VB code?
> 2.	If a transaction (or tag#) covers several hours 
> would it be necessary to have two fields instead of one: 
> beginning time and ending time?  (All transactions are 
> for a full hour. no partial hours, so if 20 MW is sold 
> for hour-ending 02 through 04, the beginning time would 
> be 0200 and ending time would be 0400).  However, the MW 
> or the price may not be the same for each of the hours in 
> question.  Then each hour would have to be entered 
> separately, causing a duplication of transactionID??  
> Would I possibly need two primary keys, i.e. TransID and 
> hour??
> 3.	with this design, will I be able to query for 
> each hour, for example hour-ending 0300.?  Again, I need 
> to keep data separate for each hour, whether it be in a 
> table or via Visual Basic or whatever.  
> 4.  Do I need 2 separate transaction tables for sales and 
> one for purchases?  
> 
> I have many other questions, but thought I'd start with 
> these.  I first need to get the tables structured 
> properly.  I hope I explained myself clearly.  If not, 
> let me know.  Thanks for any help you can give.
> 
> Patti
> 
> 
> 
>

Relevant Pages

  • RE: table structure
    ... "Patti" wrote: ... > Scheduler making the transaction is not going to want to ... Do I need 2 separate transaction tables for sales and ...
    (microsoft.public.access.tablesdbdesign)
  • table structure
    ... My questionare regarding the 'Hour-ending' field. ... Scheduler making the transaction is not going to want to ... Do I need 2 separate transaction tables for sales and ...
    (microsoft.public.access.tablesdbdesign)
  • Re: double-entry bookkeeping unneeded?
    ... I'm not asking you to design a system. ... that would represent the business transaction that I described above. ... usability problem described below. ...
    (comp.object)
  • Re: Design for historical data
    ... > types of visits are really that, 3 separate types of visits, which store ... billion rows of page delivery history in a system I helped build (I know ... Good design is important, decent, well balanced hardware (disk, ... Pro SQL Server 2000 Database Design - ...
    (microsoft.public.sqlserver.programming)
  • Re: Philosophical placement of counter
    ... Depends on what you mean by 'fsm architecture'. ... readability and maintainability of the design. ... I would likely break it up into separate clocked processes just to try ...   process ...
    (comp.lang.vhdl)