-- Being new to DB design can anyone suggest how to de-compose the following
table?
tblChargeRates
CRID Descpt Daypart Weekpart Rate ConnectRate
1 Local Day Weekday 4 6
2 Local Evening Weekday 1.5 6
3 Local Day Weekend 0 0
4 Local Evening Weekend 0 0
5 National Day Weekday 4 6
6 National Evening Weekday 1.5 6
7 National Day Weekend 0 0
8 National Evening Weekend 0 0
9 Mobile Day Weekday 12 6
10 Mobile Evening Weekday 7 6
11 Mobile Day Weekend 12 6
12 Mobile Evening Weekend 7 6
13 845 Day Weekday 4 6
14 845 Evening Weekday 2 6
15 845 Day Weekend 4 6
16 845 Evening Weekend 2 6
17 870 Day Weekday 8 6
18 870 Evening Weekday 4 6
19 870 Day Weekend 8 6
20 870 Evening Weekend 4 6


Allkenny

hi,

allkenny wrote:
> -- Being new to DB design can anyone suggest how to de-compose the following
> table?
> tblChargeRates
> CRID Descpt Daypart Weekpart Rate ConnectRate
> 1 Local Day Weekday 4 6
> 2 Local Evening Weekday 1.5 6
> 3 Local Day Weekend 0 0
> 4 Local Evening Weekend 0 0
> 5 National Day Weekday 4 6
> 6 National Evening Weekday 1.5 6
Extract the columns Descpt, Daypart and Weekpart and move them to
separate tables.


mfG
--> stefan <--

I agree with Michel. It looks pretty normalized. When I think of normalized
I think it means you would not have a situation in for example a table called
MasterDataRecords where you would have fields like:

Company AccountNumber Dept Jan Feb March April May June ......

as you might see someone setup in an excel spreadsheet with amounts under
each month. In databases, that is not normalized and does not functions as a
database. It would be more like :

Company AccountNumber Dept Date Amount

and that way you can have endless records .... at least what the system can
hold and then use your queries, forms, reports and programming you will
control the data.

And of course you would have additional tables for this example ... table
Company, table AccountNumber, table Depts. With these tables you would
control what are allowable values to to accept in the MasterDataRecords
table. For example in the table Depts you will have a field [DeptNum] and
records for example with a value of 1000, 2000, 3000, 4000, 5000, 6000.
Then program it so if a record going into your MasterDataRecords table does
not have one of those values in the [Dept] field then the new record will not
be accepted. And then for date you might have another table of Periods with
fields [Month] [Year] [Open] so you can control if a record is allowed to be
added in the MasterDataRecords table based on the the field value of [Date]
when compared to the table Periods field [Open] for that month year of the
date that you want to add. For example, in the Periods table you have a
record with [Month] [Year] [Open] with values 01 2008 C and you try
to input a new record in the table MasterDataRecords where the date is
01/20/2008 then it would not be accepted because in the Periods table it
would return that the period is C for closed....and on an on....

Steven

"allkenny" wrote:

>
> -- Being new to DB design can anyone suggest how to de-compose the following
> table?
> tblChargeRates
> CRID Descpt Daypart Weekpart Rate ConnectRate
> 1 Local Day Weekday 4 6
> 2 Local Evening Weekday 1.5 6
> 3 Local Day Weekend 0 0
> 4 Local Evening Weekend 0 0
> 5 National Day Weekday 4 6
> 6 National Evening Weekday 1.5 6
> 7 National Day Weekend 0 0
> 8 National Evening Weekend 0 0
> 9 Mobile Day Weekday 12 6
> 10 Mobile Evening Weekday 7 6
> 11 Mobile Day Weekend 12 6
> 12 Mobile Evening Weekend 7 6
> 13 845 Day Weekday 4 6
> 14 845 Evening Weekday 2 6
> 15 845 Day Weekend 4 6
> 16 845 Evening Weekend 2 6
> 17 870 Day Weekday 8 6
> 18 870 Evening Weekday 4 6
> 19 870 Day Weekend 8 6
> 20 870 Evening Weekend 4 6
>
>
> Allkenny

Michel
ConnectRate is dependant on there being a Call, except weekend calls of
Local & National description are free. Thus both Rate & ConnectRate are 0 in
these cases.

I reckoned that records from 9 down could be separated out ,but am uncertain
how to make the Join.
Using Accesses Table Analyser throws up a solution but it proves difficult
to follow.

Cheers

--
allkenny


"Michel Walsh" wrote:

> Hard to say, but it seems that ConnectRate is dependant of Rate, so I would
> make a table, Rates, with fields Rate and ConnectRate, while this table,
> chargesRates, will only get the Rate (since it seems we can get the
> ConnectRate knowing only the Rate: if rate = 0, connectRate = 0, else,
> connectRate = 6)).
>
> Otherwise, your table seems already normalized, even if you can normalize it
> further, but that may be excessive: using a table Descriptions, one field,
> Description, 5 records: Local, National, Mobile, 840, 870; same with
> DayParts, one field, dayPart, two records: Day, Evening; same with
> Weekparts, one field, weekpart, two records: Weekday, Weekend. Normalization
> is not a religion, neither an absolute science which stands all by itself:
> it depends on the use you will make of your database. As example, if you
> plan to have many 'situations' where the 'Description' will be implied,
> then, the table Descriptions is probably required and normalization about it
> is far from being excessive anymore, and probably become mandatory. On the
> other hand, if 'Description' is not used anywhere else in your application,
> then using a table Descriptions to store all its possible appearance in this
> table is excessive, for the problem as it stands, today, even if it may be
> nice in the future, if other requirements come to life. Fortunately, with
> Access, it is easy to make such changes.
>
>
> Vanderghast, Access MVP
>
>
> "allkenny" <(E-Mail Removed)> wrote in message
> news:AD3C5DD8-6C7A-4C50-B312-(E-Mail Removed)...
> >
> > -- Being new to DB design can anyone suggest how to de-compose the
> > following
> > table?
> > tblChargeRates
> > CRID Descpt Daypart Weekpart Rate ConnectRate
> > 1 Local Day Weekday 4 6
> > 2 Local Evening Weekday 1.5 6
> > 3 Local Day Weekend 0 0
> > 4 Local Evening Weekend 0 0
> > 5 National Day Weekday 4 6
> > 6 National Evening Weekday 1.5 6
> > 7 National Day Weekend 0 0
> > 8 National Evening Weekend 0 0
> > 9 Mobile Day Weekday 12 6
> > 10 Mobile Evening Weekday 7 6
> > 11 Mobile Day Weekend 12 6
> > 12 Mobile Evening Weekend 7 6
> > 13 845 Day Weekday 4 6
> > 14 845 Evening Weekday 2 6
> > 15 845 Day Weekend 4 6
> > 16 845 Evening Weekend 2 6
> > 17 870 Day Weekday 8 6
> > 18 870 Evening Weekday 4 6
> > 19 870 Day Weekend 8 6
> > 20 870 Evening Weekend 4 6
> >
> >
> > Allkenny
>