Database Design

From: adrenaline <member30922_at_dbforums.com>
Date: Thu, 12 Jun 2003 05:55:22 +0000
Message-ID: <2993046.1055397322_at_dbforums.com>

Hello!

I’m quite green with databases and I would need some help. I have some excel sheets that include calculations that are made with VBA macros. I have to move the figures and calculations to MS SQL Server 2000 database, and make a web-based user interface too.

With the new program user can choose components and construct products with them. After the wanted components are chosen, it should make some calculations with the selected components like investment costs, environmental costs, revenues etc. The program should calculate the values for 10 years ahead from this moment.

All the inserted values must be saved to the database, so user can do changes to older calculations. No calculations are saved in the database, only the needed values, so calculations are made in select-statements.

What do you think about construction of the database I’ve done? How would you do the relations and diagrams? What could be done better? What about the Primary keys? I think I should use CalculationId in several tables so I can identify the the values for the specific calculation, right? And I know there's something wrong with my design.

I hope you understood what I meant and someone could help me

Calculation-table
CalculationID (PK)
FirstName (Person who inserted the values) LastName
E-mail
DateCreated
Comment

Component-table
Component number (PK)
Component name
Component Group (There are about 10 groups that are always the same) hours needed in installation
Tool costs
Unit cost
Number of units
Failure rate
Comment
Replacement time

Environmental variables

Cost/Oily waste ($/kg)
Cost/Fluorescent tube ($/kg)
Cost/Battery ($/kg)
Cost/Electronics ($/kg)

Work hours for environmental (h)
Oily waste (kg)
Fluorescent tubes (kg)
Batteries (kg)
Electronics (kg)

Maintenance
Labour cost/normal working hour ($/h)
Labour cost/overtime working hour ($/h)
Variable cost/hour ($/h)
Average travelling time / Call-out (min)

Remote monitoring
Service centre investment ($)
Equipment investment ($)
Service centre costs / product / year ($) Telecom costs / year ($)

Operating values
Energy consumption / year (kWh)
Cost / energy consumption unit (cents / kWh)

Modernisation
Component group
Component number
Material disposal costs ($)
New material costs ($)
Working hours for modernisation (h)
Modernisation Profit ($)

I’m using MS SQL Server 2000

--
Posted via http://dbforums.com

Received on Thu Jun 12 2003 - 07:55:22 CEST