Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unanswered: Assistance with database design

    I have created a few databases and such and have had to create and recreate them numerous times before I was happy, and still found that I was not thrilled with the db as a whole. And so, I have come here to bask in the glow of all ye who are better than me in the hopes of obtaining the grail of which I seek.

    I am trying to create a database for work that will house our inventory. Why don't I use one that is already created you ask? That would defeat the learning process, and besides, my level of php programming is far below what I see out there.

    Anyway, it will house all of our inventory, software and hardware. While this is fairly simple to create, the issue is when I start trying to put in place some tracking capabilities. I want to be able to input all information about a computer when it arrives, be able to pull up warranty status, all that info, bu also everywhere the unit has been within the company throughout its life. Then when we sell, auction or recycle the item, I would like to be able to see that as well. I have toyed with everything from moving the information to historical tables to having fields that are only populated at various times and such, but nothing seems very good to me.

    Any suggestions?

    I can provide my current db and tables if it will help.

    -Mac

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Macinslaw
    Anyway, it will house all of our inventory, software and hardware. While this is fairly simple to create, the issue is when I start trying to put in place some tracking capabilities. I want to be able to input all information about a computer when it arrives, be able to pull up warranty status, all that info, bu also everywhere the unit has been within the company throughout its life. Then when we sell, auction or recycle the item, I would like to be able to see that as well. I have toyed with everything from moving the information to historical tables to having fields that are only populated at various times and such, but nothing seems very good to me.

    Any suggestions?
    If you have a database already set up then just add a log table that holds the the owner of the hardware (ie dept and perhaps an employee) and the date they received the hardware. When it moves to another department then just insert a new record with the new details.

    Software is different to hardware in many ways in it could be installed on many computers or it could be owned by the whole company rather than a department (email server). If the software moves from one user to another user then was the software removed from the original user (I doubt it). If it wasn't then is that really legal? and do you really want to be documenting illegal practises within your own company?

    Quote Originally Posted by Macinslaw
    I am trying to create a database for work that will house our inventory. Why don't I use one that is already created you ask? That would defeat the learning process, and besides, my level of php programming is far below what I see out there.
    If you're PHP programming is poor then how do you expect to use this database when you've created it?

    I can help you out on the value of your hardware/software when you come to resell it - it will be zero. Speak to your tax people to find out better ways of avoiding loss - usually involves storing the hardware in a dank basement for tax reasons.

    Mike

  3. #3
    Join Date
    Sep 2006
    Posts
    3
    Hmmm...a log table good idea. I'll have to look into how to structure one for my use.

    I did not state that my php programming was poor, or that I could not perform the code generation, only that my php is just that, simple basic php, no perl, no python, or any other scripting language embedded, except for html. No object programming, no CSS, no views, no joins, very few (if any) functions, etc. Nothing but simple variable manipulation and nested loops. The more advanced functions I have tried to understand, but found no one who could explain them in a way I can understand. I can generally reverse engineer code and change for my uses, have done this in VBA, but cannot understand enough to write using these concepts myself. I can however write in straight php just fine.

    As for resell, we sell all items for charity, and my accounting department handles all depreciation. This inventory is only for tracking purposes and has no meaning in the accounting world. All of our software must be tracked as we try our best to stay in compliance. Yes, when we perform an install on one machine it does come off another unless we buy another license, which we do often. So, yes we do want to track all software on all machines. This I thought would best be accomplished by simply adding another line in the database for each user that has it installed, but if you have a better way, by all means, I am open to suggestions.

    Recently we ran out of storage space, and we do not wish to hold on to old junk 5+ years old anyway. We are a green company and what we do not sell for charity goes to a certified recycling company that gives a detailed report on every piece of a system and tells us where it ended up. So because of out TS/ISO regulations, we must track these things.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'd go for something like the following. I guessed you have some kind of id for all hardware and software. A record would be added each time the h/w moves to a different department. The action field might describe what's happening in the transfer ie purchased, transferred or sold. The user and dept ids will be null when then item gets sold. The software could be linked to hardware in which case you could work out the owner of the software by the owner of the hardware at that time. There's millions of ways you could model your needs but at least this is a place to start.
    • HardwareHistory : hw_id, dept_id*, user_id*, upd_time, action, price
    • SoftwareHistory : sw_id, hw_id, upd_time, action, price

  5. #5
    Join Date
    Sep 2006
    Posts
    3
    Agreed, thanks for the input. This gives me a great starting point. Thanks again.

Posting Permissions

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