Results 1 to 3 of 3

Thread: Database design

  1. #1
    Join Date
    Jun 2003
    Location
    Helsinki, Finland
    Posts
    4

    Question Database design


    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
    Last edited by adrenaline; 06-13-03 at 07:45.

  2. #2
    Join Date
    Jun 2003
    Location
    Helsinki, Finland
    Posts
    4

    Re: Database design

    Edited

  3. #3
    Join Date
    Jun 2003
    Posts
    5

    Re: Database design

    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
    Last edited by nalla; 06-16-03 at 06:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •