Results 1 to 8 of 8
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Electronic Parts Database

    I'm designing a database for electronic parts for personal use. I have maybe 1000 parts, resistors, capacitors, Transistors, Diodes, Hardware and Misc all in separate tables, with PartID, PartName, PartType, PartNotes, PartsLocation, Projects, some tables have unique fields, as PartValue, PartAmp, PartsVolts, PartsPackage, DataSheet. I need to know if I'm doing this correct? Should I start with one PartsTable and just link to PartsType? Need to get started in the right direction, or maybe I'm over thinking this. I do understand how to set relationships and add lookups. Searches are a factor. Thanks for any help!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i would have all in 1 tParts table, with [partType]
    capacitor
    diode
    etc

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    You may go further adding a table for part types, e.g.
    tblParts: PartID, PartType, PartName, PartNotes;
    tblPartTypes: PartType, TypeText;

    About parts locations. As you hardly register every transistor or diode individually, the place for this field is hardly in parts table. You need instead something like:
    tblPartsMovements: PartMovementID, MovementType, MovementDate, LocationID, PartID, PartQty; (MovementType can have values 1 for incoming and -1 for outgoing movement)
    tblLocations: LocationID, LocationText;
    It covers also cases where same part can have many locations, and can be in different quantities in given location at different time moments (to get a quantity at given time moment you add all earlier incoming quantities and distract all earlier outgoing ones)

    PartValue (or better Price) is separate case. It may differ over time, so you need a separate table again:
    tblPartPrices: PartPriceID, PartID, PriceDate, Price;

    Now type-related are left. My advice is, have a separate table for them:
    tblPartUnique: PartUniqueID, PartID, PartType, UniqueFieldType, UniqueFieldValue;
    tblUniqueFieldTypes: UniqueFieldType, UniqueFieldText.
    Here I assumed, the data type for all unique fields is same - e.g. numeric. Having different data types makes design more complicated but is doable (really you need to cover 2 data types then - numeric and text).
    A part defined in tblParts can have none, one, or several linked rows in tblPartUnique - i.e. one-to-many relation, outer join.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    What exactly do you do with these Parts?
    Best to start with a clear description in plain English of the "business' this proposed database is intended to support.

  5. #5
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    This is for personal use in Projects at home, Arduino projects mainly. I've been viewing many of the videos on youtube. I see I would want to normalization and 1nF,2nF … so I'm looking at One Parts_tbl linked to PartsType.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There is a lot of Database Design info here.
    I suggest you look at the tutorials from RogersAccessLibrary mentioned in the link.
    Work through one of the tutorials to experience the process. Then write a clear description and follow the process with your own data/requirement.
    You will learn by working through the tutorial (or 2).

  7. #7
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    Dr Soper's material is great for concepts and context. I don't mean to be negative, but you must understand the basics of database before jumping too quickly into physical Access. You will find that, after the Soper videos, working through a tutorial or 2 and then using your own description and the process from the tutorial is a learning experience. It will help put the database concepts into focus and get you a "testable" database design.

    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Machine Parts Database
    By johnomar in forum Access
    Replies: 1
    Last Post: 04-24-2017, 04:04 AM
  2. Parts Database Theory.
    By Homegrownandy in forum Access
    Replies: 10
    Last Post: 11-14-2016, 08:27 AM
  3. Products and Parts Database
    By aesp533262 in forum Database Design
    Replies: 13
    Last Post: 11-11-2012, 08:07 AM
  4. Replies: 3
    Last Post: 08-02-2012, 11:37 AM
  5. Electronic ledger database design
    By conjoa in forum Database Design
    Replies: 1
    Last Post: 07-31-2012, 05:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums