[adrenaline]

Task is to make a Database and a web based user interface for components and products Life Cycle Costs.

User logs in, selects needed product type and components, and fills in all needed values for calculations. The values are inserted in separate pages, like User information insertion page, elevator information page etc. A preview page for all inserted values would be nice, but not mandatory if this is too difficult. How this could be implemented? A temporary table maybe?
Program makes the calculations annually for the next 10 years for each component and component group. Program calculates the values also separately for each category, like Environmental costs, maintenance costs, Investment costs etc.
All the projects are of course saved to the database and modification of the older projects should be possible too.

ALL THE CALCULATIONS ARE MADE IN VIEWS / SELECT STATEMENTS FROM GIVEN VALUES, SO NO RESULTS ARE SAVED TO THE DATABASE!



Table information:

User table: Holds information about users
Component table: Holds information about the components
Product information table: Holds information about the product
Product information table II: Holds information about the product
Project table: Holds information about the project
Environmental table: Holds information about the environmental values
Maintenance: Holds information about the maintenance values
Remote monitoring: Holds information about remote monitoring values
Modernisation: Holds information about the modernisation values
(Other variables table: Holds information about other variables, like discount rate)?

Here’s some tables I’ve done. I know everything isn’t right, so could you give me some advice? What would you do differently?

User table
· UserID (PK)
· User FirstName
· User LastName
· User E-mail
· User Phone
· User Unit

Component table
· Spare part number (PK)
· Spare part Name
· Spare part more spesific name
· Component group
· Spare Part comment
· date part installed?
· part price (unit cost)
· Man hour needed in installation
· Tool costs
· Number of units

Product information table
· ProjectID? (PF)
· Product type (standard, type1, type2…) The components available are depending on product type
· Component1 (All the Components are chosen from ComboBox)
· Component2

Product information table II (product variables)
· ProjectID (PF)
· Speed (m/s
· Etc.

Project-table
· ProjectID (PK) (Autonumber)
· Project Name
· Date started
· Date last updated

Environmental costs
· ProjectID? (PF)
· Cost/Oily waste ($/kg)
· Cost/Electronics ($/kg)
· Work hours (h)
· Oily waste (kg)
· Electronics (kg)

Maintenance costs
· ProjectID? (PF)
· Maintenace cotract type (There are two different contract types. Contract type affects to the costs)
· Labour cost/normal working hour ($/h)
· Labour cost/overtime working hour ($/h)
· Maintenance hours (h)

Modernisation costs
· ProjectID? (PF)
· Component group
· Material disposal costs ($)
· New material costs ($)
· Working hours (h)
· (Modernisation Profit ($))?

Other variables
· ITE-factor in installation
· Discount rate

Revenues
· Selling price
· Service contract ($ / Year)

Etc.


How would you do the relations and diagrams? What could be done better? What about the Primary keys

I’m using MS SQL Server 2000

[adrenaline]

Edited

[nalla]

Hi,

You might get additional insights by starting your analysis with use cases and identifying components and then identifying database tables. For instance, you can start off like:

==============
Write use cases:

1. member -> (create product) << uses >> (login)
2. admin -> (create product type)
3. admin -> (compute 10yr projection)

==============
etc.

Please see: http://dbforums.com/t816211.html for an example of how you can continue on the above lines.

Nalla