Results 1 to 2 of 2
  1. #1
    Join Date
    May 2017
    Posts
    1

    Unanswered: Help designing Database

    Hello,

    I am designing my first Database in Microsoft Access to help my company organize their products. The way the products are done however, I'm not sure the best way to design it so that it is easy to manage, but robust. So, I was looking for some advice from more veteran minds.

    I need to be able to store information on each product we sell. They are electronic devices, so they each come with their own serial number. Example, unit 51403 should be stored with information on which customer we sold it to, what the name of the product is etc, as well as QC testing information such as the firmware number.

    So, the first thing that comes to mind is a table that stores every unit in a format such as this:

    Serial N | Customer | Firmware | Unit Type
    51403 Bob 5.14 Duck 345

    I do run into some problems though.

    1. There are some units that are different types, but have the same serial number.
    2. We have a lot of unit types that are custom made. Example, Duck 345 versus Duck 345a, Duck 345b, Duck345/Tail.
    3. Everything is currently saved in Excel spreadsheets that are fairly scattered, and written by different people who all type differently, but I need to be able to bring all of this information into one database. I can manually type these at first, but I want to have the database design down first before I start the busywork.
    4. Each unit type has different QC testing information, so one table for all units doesn't seem like it will work. The testing information is generated by software which exports a .csv file by the way.

    I am thinking that I might have to have a separate table for each Unit Type to store the QC testing information, then store each individual unit in a master table of some sort with a table lookup back to its testing information, but that feels redundant to me? I know for sure that I want Customers and Unit Types to be hard written into the database, and the user selects one from a list so that different users don't end up typing it differently.

    I probably haven't laid out enough information here, so if I can explain it any more, I can, but let me know if anyone has advice on how I can design this.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    602
    Provided Answers: 31
    Why would they make products with the SAME serial#?
    the whole point of the serial is to make it unique.
    If you have no unique ID, then you must use autonumber.

    The tProduct table will have every individual unit, NAme,Type, ID.
    the tTesting table can import from .csv.

Posting Permissions

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