May 7, 2007 at 11:33 am
Hi,
i need help on this . i have to create Tables with the following information below in a relational Database.Can somebody explain me me how many tables do i need to create and which one .
| Electricity | |||||||||||
| 2004 | 2005 | 2006 | |||||||||
| Usage | Demand | Total Cost | Usage | Demand | Total Cost | Usage | Demand | Total Cost | |||
| Jan | |||||||||||
| Feb | |||||||||||
| Mar | |||||||||||
| April | |||||||||||
| May | |||||||||||
| June | |||||||||||
| July | |||||||||||
| August | |||||||||||
| Sept | |||||||||||
| Oct | |||||||||||
| Nov | |||||||||||
| Dec | |||||||||||
| Fuel | ||||||||
| 2004 | 2005 | 2006 | ||||||
| Gallon Delivered | Total Cost | Gallon Delivered | Total Cost | Gallon Delivered | Total Cost | |||
| Jan | ||||||||
| Feb | ||||||||
| Mar | ||||||||
| April | ||||||||
| May | ||||||||
| June | ||||||||
| July | ||||||||
| August | ||||||||
| Sept | ||||||||
| Oct | ||||||||
| Nov | ||||||||
| Dec | ||||||||
May 7, 2007 at 11:42 am
How many records do you anticipate having? 10's or Millions?
How will you be using the data?
Do you have any requirements that you can pass along?
Your needs will dictate the solution.
May 7, 2007 at 11:52 am
The information above shows For example electricity cost for three year for every month and thats it.That is all information i have .
May 7, 2007 at 12:05 pm
If you are only talking about your electric bill, then just use a spreadsheet.
I really do not understand what you are asking.
May 7, 2007 at 12:07 pm
You need one table for the gaz and one for the electricity.
The basic format will look like this :
clientID, ReadDate, Quantity, CostPerUnit (can be in another table with cost, datefrom and dateto, but it adds another join on the report which can be a bottleneck).
Then you basically build a proc that groups the data by month of the year and clients. As it's already been said, you might have to create reporting tables where you will ship the data once and keep for history and reporting sake. Or if you have very little data, then you can recalculate on the fly everytime. Both method works, it's just up to you to decide wether you want to spend more time on developping the system, or having the server work more. This will depend solely on the amount of data and the amount of reporting required of that data. I can't help you much on that at this point because I don't have any figures to go by.
May 7, 2007 at 12:20 pm
OK,
I guess it is getting ConFUSING. let me tell you the solution and i just want to know whether iam on the right track or not.
Inthis case what iam doing is .
iam creating the following tables
Month_Table --------which has the months from jan-dec
Year_Table --------which has years from 2004-------2006
Category_Table------Which has the following two categories Electricity,Fuel oil
Electricity_Table-----which has information such as usage,demand,Total cost e.t.c
Fuel oil_Table--------which has information such as Gallon Delivered,Total Cost
does this makes a little clear what iam trying to ask?(becz Relational database)
May 7, 2007 at 12:33 pm
Month and year table are usefull to do a left join so that months and years without data can still be displayed. The category table doesn't make much sens to me because the Electricity and fuel tables are seperated (any other data you are not sharing with us).
What's left to do is decide if you want to have permanant reporting tables which hold the calculated results from the queries that create the reports. This will hold the answer as to whether you need another set of 2 tables or not (you don't need one per year, just keep the year in a column in the report, making it part of the clustered index along with the month).
May 7, 2007 at 1:03 pm
Please remember we are not playing Jeopardy here, where you give the solution and we guess what the problem is.
If you want the proper help you will have to tell us what the PROBLEM is, not what the SOLUTION needs to be.
Do you have to store data about your customers Electricity or Fuel Oil usage?
What details about each customer will you need to track? What would comprise a singular record?
What is the scale (how many rows) are you looking to store?
May 7, 2007 at 5:34 pm
You need 4 tables:
Table dbo.Calendar to hold all possible dates;
CREATE TABLE dbo.Resource (
ID smallint IDENTITY(1,1) NOT NULL RPIMARY KEY,
Name nvarchar(100) NOT NULL
)
to hold the list of resources you manage (currently "Electricity" and "Fuel");
CREATE TABLE dbo.UOM (
ID smallint IDENTITY(1,1) NOT NULL RPIMARY KEY,
Name nvarchar(100) NOT NULL
)
to hold all units of measure, including money currencies;
CREATE TABLE dbo.Usage(
ResourseID smallint NOT NULL,
DateUsed datetime NOT NULL,
Demand DECIMAL (18, 4) NULL,
UOM_ID smallint NOT NULL,
Price money NOT NULL,
CurrencyID smallint NOT NULL,
Usage DECIMAL (18, 4),
TotalAmount AS CONVERT(money, Price * Usage )
)
Then you need single table function which will take @ResourceID, @DateFrom, @DateTo as parameters and return aggregations for the period.
_____________
Code for TallyGenerator
May 7, 2007 at 7:49 pm
Spot on, Serqiy!
--Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply