基于Web的零件库存设计

Question

I am a field service technician and I have an inventory of parts that is either issued to me by the company I work for or through orders for specific jobs. I am trying to design a website to manage my parts, both on-hand inventory and parts that have been returned or transferred to someone else. Here is the information I need to track:

• part number(10 digit)
• req number(8 digit, unique)
• description(up to 50 characters)
• location(Van or shed).
• WorkOrder("w"+9 digits ex: 'W212141234')
• BOL(15 digit bill of lading #)
• TransferDate(date I get rid of part)
• TransferMethod(enum 'DEF','RTS','OBF')

I will probably use PHP to make a website and interact with the MySQL database.

What is the best design? A multi-table approach or one table with webpages that display queries of only certain fields? I need a list of on hand parts that list part number, req number, description, and location. I will also need to be able to have "defective returns" view that will list what parts I returned as DEF with all the remaining fields filled in.

Besides the "on hand" fields, the rest of the fields won't have data until they are no longer "on hand".

I really appreciate any help because I am new to both SQL and PHP. I have experimented with Ruby on Rails and django but I am not sure if I need to tackle all that at this point.

Answer 1

Even though you give some information on your issue, it is hard to actually approach it as the question itself on "what is the best design" is vague.

What I would do is this:

MYSQL TABLE DESIGN

Table parts

req number(int(8), unique, KEY) 
part number(int(10))
description(varchar(50))
location(enum 'Van','shed')
WorkOrder(varchar(10))
BOL(varchar(15))
TransferDate(date)
TransferMethod(enum 'DEF','RTS','OBF')
onhand (boolean)

PHP SCRIPTS

and then i would make 2 php scripts with a single query each and a table displaying the info

onhand.php

select *fields filled for on hand parts* from parts where onhand = 1

notonhand.php

select *fields filled for not on hand parts* from parts where onhand = 0