RE: table structure

From: Patti (anonymous_at_discussions.microsoft.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 08:58:17 -0700

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
        .
        .
        .
         HE24

I think it would be easier for the trader to have a
single transaction as their form (Again, I know I am
jumping ahead to forms design, but I know somewhat how we
want the form to look like, just don't know how to design
the tables to get there..

Is it possible to link the hours that have MW and $$ data
entered, back to the single "HourEnding" field in the
table, possibly each hour for each day as a separate line
item? I don't think having 24 separate fields for each
hour is the right answer???? But I really don't want the
trader to have to enter the actual hour every time they
set up a transaction. It would be much too cumbersome
for them. Also, they can be entering schedules for more
than one day. Due to their time constraints, it's very
important that the trader enter as little redundant
information as possible.

The data is currently being entered in a daily
spread*** so the calculations are showing up
immediately for them. However, reports, etc. are very
limited (each day is saved in a separate file with the
date as the filename so monthly calculations are not
fun!!!) so I really feel a database is the right answer.

I'm sorry this is getting so long, but as you can see,
this could turn into a very complex situation. Thanks in
advance for any help you can provide. If you need
further info, let me know.

THANKS! Patti

>-----Original Message-----
>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?Tt 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?Ts CompanyID to the SellingCompanyID
field and the second version?Ts CompanyID to the
PurchasingComapanyID field.)
>
> 1. Base a form on the above table and your
scheduler can enter an entire day?Ts 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?Tt allow for a Tag#,
but if there are no rules for the creation of a ?
oTagNumber?, you could just combine the TransDate with
the two CompanyID?Ts in a text field on a report or
form. You wouldn?Tt 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
>>
>>
>>
>>
>.
>