0

Good afternoon. Let me preface this by saying I am not a DBA, I know just enough to get by and possibly get myself into trouble. I am being tasked with getting equipment inventory into an existing database that does not currently contain this information. My employer currently uses JotForm to collect inventory information. I have added all the column headings shown when you extract the data into CSV or XLS at the bottom.

I feel like all the information from this extract would not go into just one table, but possibly multiple tables based on the data. This has not been an easy sell to my manager as she really just wants "a place to dump the information we get from the form". I am not a fan of just dumping bad data and then possibly having to clean it up later. I created an "Equipment" table so that we can list all the equipment we currently have listed on our bid sheets for job locations. I started simple and just have Item #, Type, Manufacturer, Model, Description, Serial #, and Cost. I have attached the code (I hope) that I used to create this table, just in case

1][1]Code Screenshot

Here is my question, how many tables do you think are actually needed in this instance? I know we have a table that would include Job Site # and Location, but everything else will be completely new. I really appreciate any guidance provided, and will update with any additional information I can.

Submission Date
Reason for Entry
Job Site #
Location
Equipment Type
Make/model (if known)
Serial Number
Description
Condition
Additional information you would like to share about this equipment
Some Jobsites don't have any power equipment.
Vendor
Inventory Year
Hours on machine - if available.
email for confirmation receipt - this email will receive verification that form was submitted 2nd email for additional confirmation receipt.
Company
Invoice #
Tax
Cost per unit
GL Code
Invoice Date
Freight

6
  • Hi, and welcome to dba.se! What you have above is the start of an embyonic ERM/ERP system - that's a lot of work! I thnk it's beyond the scope of this (Q&A) forum to provide what would, essentially, be a consulting gig! If you do decide to embark on this root, by all means come back to us with more specific questions, but I would hesitate to go down that route - I've worked with a few and they're massive undertakings. What are the available resources (tech, $$ & manpower)?
    – Vérace
    Commented May 2 at 23:18
  • It is a super small company, they outsource the actual DBA stuff. I am just taking the "low hanging fruit" as she says. I am probably over thinking it. She really just wants the physical inventory for equipment in the database. It will just be an import job once the CSV is extracted from JotForm. I will probably just do that and then we can go from there. Commented May 3 at 12:47
  • OK - not sure how an "email for additional confirmation receipt." is part of any physical inventory? "equipment" can cover a multitude - is that a bulldozer or a transformer or a screw? Or will there be some type of BOM where you have the master item composed of sub-items which themselves are composed of sub-sub-items... Bulldozer -> Engine -> Cylinder Block.... down to the screw...? Sort your entities into classes and make tables of each class - that should get you started. As for the number of tables, only you can answer that depending on your requirements. ERP/ERM systems are...
    – Vérace
    Commented May 3 at 14:44
  • ... normally very flexible, but pay a penalty (behind the scenes - and sometimes not so much behind them) for this - if you have a very specific (and relatively simple) set of requirements, a custom setup which covers these may be fine - again, only you can tell! Best of luck in your endeavour!
    – Vérace
    Commented May 3 at 14:44
  • @Vérace All those fields were pulled from the JotForm they use as a front end, it was a very confusing form. I am cleaning up the data and deciding what actually needs to go into the database. Commented May 3 at 14:50

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.