table structure

[Patti]

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

 

[Guest]

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.

 

[Patti]

Thanks. That was helpful . It got me "thinking outside
my box!!", however, I realize I didn't give enough
information.

Here's some more background information:

An estimated load (MW) available for each hour is
determined ahead of time (day before or several days
before) based on a number of factors. That load changes
on an hourly basis due to weather, plant capabilities,
etc. so the trader needs to know, on an hourly basis,
how much load he is working with so he knows how much
additional MW he needs to buy or sell for the next (or
several) hours. So a 2nd dynamic form may need to be
displayed at all times showing the "balance" of MW
available to buy or sell? That was going to be one of my
other "many, many more questions" that I referred to in
my first post. I was afraid of bombarding you with too
much information "overload" so didn't mention that the
first time!!! I think I need to deal with the basics
first. (Just trying to write this post to make it easy
for someone else to understand is challenging!!!).

As far as the table structures, each transaction can
cover many hours. Also, a company can have more than one
transaction in a day. I'm not sure at this point if its
critical or not, but off the top of my head, it seems
like it would make more sense to keep the sales and
purchases separate so the hourly load can be calculated
easier? Maybe not in the table structure, but in the
data entry form. Without rambling on too much, I think
my main question right now is:

If I have a form that looks something like this (hours
vertical or horizontal on the form isn't important) so
the trader can cursor to the hours in question, and just
enter MW and $$:


Date:
Company:
ScheduleType:

HourEnding MW Price
HE01
HE02
HE03

 

[Guest]

Hi Patti,

I built a little sample solution just to make sure my idea would actually work. There are a lot of elements to getting this to go, but it ends up in a result that I think you will appreciate. In the following, the transaction table has the following structure.

tblTransaction
TransID
TransDate
TransTime
SellID
BuyID
MW
Price

1. Create a table named tblDates. Have one field "theDate" and designate it as the PK.

2. Create another table named tblTime. Have one field "theTime" and designate it as the PK.

3. Create a module and add the following (watch out for word-wrap here):

Option Compare Database
Option Explicit

'This function will populate a table with dates as specified in
'the "For..." line of the code.
'There must first be a table (tblDates) with a field name (theDate)
'for the function to work.
Dim db As DAO.Database
Set db = CurrentDb
Dim datDate As Date
For datDate = #6/1/2004# To #6/30/2004#
db.Execute "Insert into tblDates (theDate) Values (#" & datDate & "#)", dbFailOnError
Next
'This function and the 'addTime' function should both only be used once to
'populate a table.
End Function
Function addTimes()

'This function will populate a table with times as specified in
'the "For..." line of the code.
'There must first be a table (tblTime) with a field name (theTime)
'for the function to work.
Dim db As DAO.Database
Set db = CurrentDb
Dim datTime As Date
Dim intLoop As Integer
'create 60 minute intervals
For intLoop = 0 To 23
db.Execute "Insert into tblTime(theTime) " & _
"Values (#" & DateAdd("n", intLoop * 60, 0) & "#)", _
dbFailOnError
Next
End Function

4. Run each function ONCE, then disable the functions by marking each line as a comment.

5. Create a new query. Add tblCompany twice, tblDates, and tblTime. Drag theDate, theTime, CompanyID, and CompanyID_1 to the grid. Change the query to an append query. Append the fields to the appropriate fields in tblTransaction.

6. Create your form based on tblTransaction. Unbound the TransDate, SellID, and BuyID. Change the SellID and BuyID textboxes to combos based on tblCompany. Add a listbox based on tblTime and size it so that all 24 times are displayed. Modify the Record Source of the form so that it is a query based on tblTransaction. Set the criteria for TransDate to match the form's matching textbox. Set the criteria for SellID and BuyID to match the form's matching combos. Set the criteria for TransTime to match the listbox.

7. In the After_Update event of each pertinent control (TransDate,Listbox,SellID,BuyID) requery the form.

The cartesian query creates records for each possible date / time / company1 / company2 combination. The form filters the record source down to the one record that matches the input criteria.

With this setup, the scheduler can enter a date, select a selling company, select a buying company, then click on the appropriate time in the listbox and add the MW and Price to that record.

I understand that there are a huge amount of records that will have nulls in the MW and Price fields, but this does serve your input requirements and some of the other needs that you'd posted (I think).

Please post back and let me know how this worked for you.

 

[Guest]

As I re-read your problem I think that there may be an alternative way of thinking about it.

It seems like the transaction portion of the db is parallel to the classic Order entry - your 'MasterTransaction' is the equivilent of an Order and 'Transaction' is the equivilent of OrderDetails. Your 'Products' are hours and each hour can have a different quantity (MW) and Price which are stored in the OrderDetails table. However, with this type of app, generally the "Products" are displayed in a combo and you want to 'see' all 24 hours at once....

On to you next problem, though.... Let me see if I understand this correctly.

The "Scheduler" is assigned an inventory of MW that is replenished on a regular basis. It is up to him to 'sell' those MW or they are lost. If he over-sells what his inventory allows, then it is up to him to 'purchase' more so that all of his 'sales' are covered. You want to know what the balance is in this situation?

Or is it that you want to know what the current estimated amount of MW available is? (with all of those outside factors?)

And I'm still not clear on companies - can each company both buy and sell or is there a separate list of buyers and a separate list of sellers?

 

[Guest]

A couple more things...

Can you explain "Schedule Type"? It seems to be an important issue with your structure, but I can't surmise what it's for from the information posted so far.

Also, please explain "Tag#" - is this something that the industry requires?

Plus, the more I study your needs, the less I like my cartesian query solution. Maybe you should put that to the side for now...

I'm going to put more thought into this over the weekend. Hopefully, before I leave work (and internet access) you'll have answered some of my other questions.

I'll recap my understanding of your needs in a simplified manner (and please correct me if I'm wrong):

1. You are selling one product - MegaWatts.

2. You have to purchase them, then sell them, all the while keeping track of the amount in "inventory".

3. Because WHEN is a very important criteria of the product/purchase/sale, it needs to be tracked.

4. It is the duty of the Scheduler to assign the delivery of the product to a particular hour (WHEN). Plus, you want the data entry form to be easy for the Scheduler to punch in MW quantities and prices for any individual delivery hour and still view all of the day's hours without having to navigate through combos or type in the hour.

5. You want to be able to query the deliveries for a particular hour or for a span of hours.

6. You want to query a companies purchases for a date or date range and see all of the individual deliveries within that date range.

7. You want to view 'hours' in military time.

8. You want to track "Losses" which are the result of purchasing more MW than you could sell in a given Hour.

9. The "inventory" of MW is added to automatically at the beginning of each hour (pre-purchased based on other estimated criteria) and it is lost at the end of each hour.

10. The "inventory" purchased for Hour 1600 on 6/18/04 cannot be sold to any other time/date.

Is all of the above correct?