Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2017

    Small Business Data Base

    I am not a database admin. My experience with DBs is some MySQL way back for PHP based websites, some MSSQL for things like SharePoint, and some Postgres in a previous job involving supporting Linux based point of sale systems.

    I very small electronics company that builds items, usually government sub contracts, has asked me to help out with building them a new data base.
    My mother used to work here full time and now just does some contract work for them, like helping with ISO certification recently.
    I am currently between jobs while I work on my CCNA so I do have some time.

    Anyway, they currently use several MS Access databases. The structure is something like this:

    Document Control db
    -The db just lists where docs are and their revision, associated jobs, ECNs, etc.
    -The docs themselves are in a network share
    -There is an Intranet website build and maintained in FrontPage where someone will create a link to the doc file in the network share

    Purchasing db
    -BOFs, Kitting tables, purchase orders
    -This one is a big mess

    Stores db
    -This used to be an Oracle db, but when doing a walk around with the boss, we found out someone had moved it to Access
    -Parts stores inventory. Electronics components, items made in house, etc

    ECN db
    -Engineer Change Notes
    -Discrepancy forms, ECNs, WorkOrders

    Calibration db
    -Equipment calibration logs
    -Calibration status, last date, due date, etc. No real info about result specs

    An overview of some current processes:

    POs placed from a master part list (parts approved by the customers) for a job part list, parts are received and then put into stock or kitted for assembly.
    Reports for kitting come from the db but are manual. Person in kitting checks a paper BOF (build order form) again the stores db. Deficiencies are entered into a paper doc and sent to purchasing to create a PO.
    Travelers go with kits and sub assemblies and are basically assembly instructions.
    A computer in production has a barcode scanner and workers scan their barcode and a work order at milestones, like kitting, stuffing, soldering, testing, etc.

    All of the above databases need to combined into one, aside from the calibration data base. The calib db can remain on it's own or be a table somewhere else.

    Some functionality that would be nice to add:
    Automation. Instead of many separate dbs, have one db or maybe a few that communicate together for purchasing, stores, and document control.
    Sample flow plan: Engineer designs an item. A Build Order Form is generated. Parts list from the BOF is checked against stores. Items not in stock are put into a discrepancy form and sent to purchasing.
    Purchasing can run reports and generate POs based on vendor. Items with multiple venders, maybe have a way to list pricing and lead time from each to determine which vendor would be used. This will most likely be manual calling or web lookup. No need to tie into vendor dbs or websites.
    In addition to current barcode scanning practices, maybe add some serial numbers to each job's sub assemblies and scan those through each mile stone.
    Document control is currently so... complicated and antiquated. A new database that can handle documents uploaded directly to it and use job numbers to pull together a work order (CAD drawings, kitting forms, travelers, etc) would be very nice to have. Also store documents such as manuals, calibration procedures, test procedures, etc.

    Current software is Windows 2K, XP, 7 and 10 on various machines.
    Access 2003 and 2013.
    No internet access on the cabled LAN. Only internet on some computers using Wifi.
    No Active Directory.
    Only server in the company is Win2K and it is a file server. Other things are hosting on workers desktops.

    New hardware will need to be purchased for a new db. Software as well.
    I can provide some older but still usable servers and plenty of RAM (up to 64GB), so my thought was install a hyper visor and add virtual systems as needed to support the data base and possibly other services.
    For software, cost is a consideration. So cost of a Windows Server license vs cost of Linux.
    Cost of MS SQL vs a cheaper or open source data base.
    Also cost of building and maintaining on those architectures from a labor perspective. Accessibility across various versions of Windows.

    What I've listed is in no way the complete database or functions. Just what I can remember or put into words.

    With what is in place now and where things need to be, what would be the recommended database to use? What about the front end? Off the shelf interface customized, custom built interface, web browser based interface?

    This project may be more than I can do from a knowledge and experience stand point, but maybe I can at least inform the company what can/should be done.

    I much appreciate any input.

  2. #2
    Join Date
    Dec 2015
    Guisborough, England
    Well, this is a pretty large 'ask', and it's something that has already got out of hand, and could easily get even more out of hand.

    I'd assume that as the 'system' (in so far as it is a 'system') is 'sort-of' working, and as I assume too much downtime would NOT be convenient, I'd be incluned towards an evolutionary approach.

    You say there IS a network - although you don't say what is connected to the network - and Win 2k is at least useful, so you could build on that. One of the systems I've set up is still running OK on win 2003 server I believe and it manages OK if a couple of provisos are monitored.

    Anyway, I'd look to bring certain processes into the network/server, based around a solid system core, that you can then add further processes to as the system grows. I'd expect too that as the system develops, the business will ask for more 'extras', so again, your core needs to bear that in mind.

    I'm not sure you could do all of the things you want within the confines of a db system, I'd suspect that you'll need some amount of raw programming, although bear in ming that something like FreeBasic (and some other things as well) can interface with MySQL.

    More thoughts later..


Posting Permissions

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