DB Normalisation

  • Thread starter Thread starter allkenny
  • Start date Start date
A

allkenny

-- 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,
-- 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
Click to expand...
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
 
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
 
Back
Top