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)?

    Heres some tables Ive done. I know everything isnt 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

    Im using MS SQL Server 2000
    Last edited by adrenaline; 06-13-03 at 08: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 07: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
  •